# -*- text -*-
#
#  ippool/oracle/queries.conf -- Oracle queries for rlm_sqlippool
#
#  $Id$

#  Using SKIP LOCKED speeds up selection queries
#  However, it requires Oracle > 11g.  It MAY work in 9i and 10g
#  but is not documented.  Uncomment the following if you are
#  running a suitable version of Oracle
#
#skip_locked = "SKIP LOCKED"
skip_locked = ""

allocate_begin = "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 = "\
	SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \
		SELECT id FROM ( \
			SELECT * \
			FROM ${ippool_table} \
			WHERE pool_name = '%{control:${pool_name}}' \
			AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \
			) \
			ORDER BY expiry_time DESC \
		) WHERE ROWNUM <= 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 id IN ( \
		SELECT id FROM ( \
			SELECT * \
			FROM ${ippool_table} \
			WHERE pool_name = '%{control:${pool_name}}' \
			AND expiry_time < current_timestamp \
			) \
			ORDER BY expiry_time \
		) WHERE ROWNUM <= 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 id IN ( \
#		SELECT id FROM ( \
#			SELECT * \
#			FROM ${ippool_table} \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND expiry_time < current_timestamp \
#			ORDER BY DBMS_RANDOM.VALUE \
#		) WHERE ROWNUM <= 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 (\
		SELECT id \
		FROM ${ippool_table} \
		WHERE pool_name='%{control:${pool_name}}'\
	) \
	WHERE ROWNUM = 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}:-0}', \
		username = '%{SQL-User-Name}', \
		expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1) \
	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 = "\
#	SELECT fr_allocate_previous_or_new_framedipaddress( \
#		'%{control:${pool_name}}', \
#		'%{SQL-User-Name}', \
#		'%{%{Calling-Station-Id}:-0}', \
#		'%{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 = current_timestamp + INTERVAL '${lease_duration}' second(1) \
	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 = current_timestamp - INTERVAL '1' second(1) \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
	AND pool_key = '${pool_key}' \
	AND username = '%{SQL-User-Name}' \
	AND callingstationid = '%{%{Calling-Station-Id}:-0}' \
	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 = current_timestamp + INTERVAL '${lease_duration}' second(1) \
	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}:-0}'"

#
#  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 = current_timestamp - INTERVAL '1' second(1) \
	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 = current_timestamp - INTERVAL '1' second(1) \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"