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

#
#  This query allocates an IP address from the Pool
#  The ORDER BY clause of this query tries to allocate the same IP-address
#  to the user that they had last session...
#
allocate_find = "\
	SELECT framedipaddress \
	FROM ${ippool_table} \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND ( \
		expiry_time < 'now'::timestamp(0) \
		OR ( nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' ) \
	) \
	ORDER BY \
		(username <> '%{SQL-User-Name}'), \
		(callingstationid <> '%{Calling-Station-Id}'), \
		expiry_time \
	LIMIT 1 \
	FOR UPDATE"

#
#  The above query again, but with SKIP LOCKED. This requires PostgreSQL >= 9.5.
#
#allocate_find = "\
#	SELECT framedipaddress \
#	FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND expiry_time < 'now'::timestamp(0) \
#	ORDER BY \
#		(username <> '%{SQL-User-Name}'), \
#		(callingstationid <> '%{Calling-Station-Id}'), \
#		expiry_time \
#	LIMIT 1 \
#	FOR UPDATE SKIP LOCKED"

#
#  Use a stored procedure to find the address. This requires PostgreSQL >= 9.5 as
#  SKIP LOCKED is used.  See `procedure.sql` in this directory for the
#  indices and stored procedure used by this query.
#
#  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_find = "\
#  /*NO LOAD BALANCE*/ \
#  SELECT find_previous_or_new_framedipaddress( \
#  	'%{control:${pool_name}}', \
#  	'%{SQL-User-Name}', \
#  	'%{Calling-Station-Id}' \
#  	)"

#
#  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'::timestamp(0) \
#	ORDER BY RANDOM() \
#	LIMIT 1 \
#	FOR UPDATE"

#
#  The above query again, but with SKIP LOCKED. This requires PostgreSQL >= 9.5.
#
#allocate_find = "\
#	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"

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

#
#  This query frees the IP address assigned to "pool_key" when a new request
#  comes in for the same "pool_key". This means that either you are losing
#  accounting Stop records or you use Calling-Station-Id instead of NAS-Port
#  as your "pool_key" and your users are able to reconnect before your NAS
#  has timed out their previous session. (Generally on wireless networks)
#  (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}:-%{Nas-IPv6-Address}}'
#  from the WHERE clause)
#
allocate_clear = "\
	UPDATE ${ippool_table} \
	SET \
		nasipaddress = '', \
		pool_key = 0, \
		callingstationid = '', \
		expiry_time = 'now'::timestamp(0) - '1 second'::interval \
	WHERE nasipaddress = '%{NAS-IP-Address}' \
	AND pool_key = '${pool_key}'"

#
#  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 frees an IP address when an accounting
#  STOP record arrives
#
stop_clear = "\
	UPDATE ${ippool_table} \
	SET \
		nasipaddress = '', \
		pool_key = 0, \
		callingstationid = '', \
		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 frees all IP addresses allocated to a NAS when an
#  accounting ON record arrives from that NAS
#
on_clear = "\
	UPDATE ${ippool_table} \
	SET \
		nasipaddress = '', \
		pool_key = 0, \
		callingstationid = '', \
		expiry_time = 'now'::timestamp(0) - '1 second'::interval \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"

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