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

start_begin = "commit"
alive_begin = "commit"
stop_begin = "commit"
on_begin = "commit"
off_begin = "commit"


#  *****************
#  * DHCP DISCOVER *
#  *****************

#
#  Use a stored procedure to find AND allocate the address. Read and customise
#  `procedure.sql` in this directory to determine the optimal configuration.
#  Oracle's locking mechanism limitations prevents the use of single queries
#  that can either find a client's existing address or the first available one.
#
allocate_begin = ""
allocate_find = "\
	SELECT fr_dhcp_allocate_previous_or_new_framedipaddress( \
		'%{control:${pool_name}}', \
		'%{DHCP-Gateway-IP-Address}', \
		'${pool_key}', \
		'${offer_duration}', \
		'%{%{${req_attribute_name}}:-0.0.0.0}' \
	) FROM dual"
allocate_update = ""
allocate_commit = ""


#
#  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} \
#			JOIN dhcpstatus \
#			ON ${ippool_table}.status_id = dhcpstatus.status_id \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND expiry_time < current_timestamp \
#			AND dhcpstatus.status = 'dynamic' \
#			ORDER BY DBMS_RANDOM.VALUE \
#		) WHERE ROWNUM <= 1 \
#	) FOR UPDATE"

#
#  The above query again, but with SKIP LOCKED. This requires Oracle > 11g.
#  It may work in 9i and 10g, but is not documented, so YMMV.
#
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \
#		SELECT id FROM (\
#			SELECT * \
#			FROM ${ippool_table} \
#			JOIN dhcpstatus \
#			ON ${ippool_table}.status_id = dhcpstatus.status_id \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND expiry_time < current_timestamp \
#			AND dhcpstatus.status = 'dynamic' \
#			ORDER BY DBMS_RANDOM.VALUE \
#		) WHERE ROWNUM <= 1 \
#	) FOR UPDATE SKIP LOCKED"

#
# A tidier version that needs Oracle >= 12c
#
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} WHERE id IN (
#		SELECT id FROM ${ippool_table} \
#		JOIN dhcpstatus \
#		ON ${ippool_table}.status_id = dhcpstatus.status_id \
#		WHERE pool_name = '%{control:${pool_name}}' \
#		AND expiry_time < current_timestamp \
#		AND dhcpstatus.status = 'dynamic' \
#		ORDER BY DBMS_RANDOM.VALUE \
#		FETCH FIRST 1 ROWS ONLY
#	) 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 "offer_duration" after which time it may be reused.
#  Only needed if allocate_find is not using the stored procedure and therefore
#  not updating the lease
#
#allocate_update = "\
#	UPDATE ${ippool_table} \
#	SET \
#		gateway = '%{DHCP-Gateway-IP-Address}', \
#		pool_key = '${pool_key}', \
#		expiry_time = current_timestamp + INTERVAL '${offer_duration}' second(1) \
#	WHERE framedipaddress = '%I'"


# ****************
# * DHCP REQUEST *
# ****************

#
#  This query revokes any active offers for addresses that a client is not
#  requesting when a DHCP REQUEST packet arrives
#
start_update = "\
	UPDATE ${ippool_table} \
	SET \
		gateway = '', \
		pool_key = '0', \
		expiry_time = current_timestamp - INTERVAL '1' second(1) \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \
	AND expiry_time > current_timestamp \
	AND ${ippool_table}.status_id IN \
	(SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')"
start_commit = "COMMIT"

#
#  This query extends an existing lease (or offer) when a DHCP REQUEST packet
#  arrives.  This query must update a row when a lease is succesfully requested
#  - queries that update no rows will result in a "notfound" response to
#  the module which by default will give a DHCP-NAK reply.  In this example
#  incrementing "counter" is used to achieve this.
#
alive_update = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1), \
		counter = counter + 1 \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'"
alive_commit = "COMMIT"


# ****************
# * DHCP RELEASE *
# ****************

#
#  This query frees an IP address when a DHCP RELEASE packet arrives
#
stop_clear = "\
	UPDATE ${ippool_table} \
	SET \
		gateway = '', \
		pool_key = '0', \
		expiry_time = current_timestamp - INTERVAL '1' second(1) \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{DHCP-Client-IP-Address}' \
	AND ${ippool_table}.status_id IN \
	(SELECT status_id FROM dhcpstatus WHERE status = 'dynamic')"
stop_commit = "COMMIT"


#
#  This query is not applicable to DHCP
#
on_clear = ""


# ****************
# * DHCP DECLINE *
# ****************

#
#  This query marks an IP address as declined when a DHCP Decline
#  packet arrives
#
off_clear = "\
	UPDATE ${ippool_table} \
	SET status_id = (SELECT status_id FROM dhcpstatus WHERE status = 'declined') \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{DHCP-Requested-IP-Address}'"
off_commit = "COMMIT"