# -*- text -*-
#
#  ippool-dhcp/mssql/queries.conf -- MSSQL queries for rlm_sqlippool
#
#  $Id$

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

#
#  This series of queries allocates an IP address
#

#
#  MSSQL-specific syntax - required if finding the address and updating
#  it are separate queries
#
#allocate_begin = "BEGIN TRAN"
#allocate_commit = "COMMIT TRAN"

allocate_begin = ""
allocate_commit = ""

#
#  Attempt to find the most recent existing IP address for the client
#
allocate_existing = "\
	WITH cte AS ( \
		SELECT TOP(1) framedipaddress, expiry_time, gateway \
		FROM ${ippool_table} WITH (xlock rowlock readpast) \
		JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
		WHERE pool_name = '%{control:${pool_name}}' \
		AND pool_key = '${pool_key}' \
		AND dhcpstatus.status IN ('dynamic', 'static') \
		ORDER BY expiry_time DESC \
	) \
	UPDATE cte \
	SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
	gateway = '%{DHCP-Gateway-IP-Address}' \
	OUTPUT INSERTED.FramedIPAddress \
	FROM ${ippool_table}"

#
#  Determine whether the requested IP address is available
#
allocate_requested = "\
	WITH cte AS ( \
		SELECT TOP(1) framedipaddress, expiry_time, gateway \
		FROM ${ippool_table} WITH (xlock rowlock readpast) \
		JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
		WHERE pool_name = '%{control:${pool_name}}' \
		AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
		AND dhcpstatus.status = 'dynamic' \
		AND expiry_time < CURRENT_TIMESTAMP \
	) \
	UPDATE cte \
	SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
	gateway = '%{DHCP-Gateway-IP-Address}', \
	pool_key = '${pool_key}' \
	OUTPUT INSERTED.FramedIPAddress \
	FROM ${ippool_table}"

#
#  If the existing address can't be found this query will be run to
#  find a free address
#
allocate_find = "\
	WITH cte AS ( \
		SELECT TOP(1) framedipaddress, expiry_time, gateway, pool_key \
		FROM ${ippool_table} WITH (xlock rowlock readpast) \
		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 expiry_time \
	) \
	UPDATE cte \
	SET expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
	gateway = '%{DHCP-Gateway-IP-Address}', \
	pool_key = '${pool_key}' \
	OUTPUT INSERTED.FramedIPAddress \
	FROM ${ippool_table}"

#
#  Alternatively attempt all in one, more complex, query
#
#  The ORDER BY clause of this query tries to allocate the same IP-address
#  which user had last session.  Ensure that pool_key is unique to the user
#  within a given pool.
#
#allocate_find = "\
#	UPDATE TOP(1) ${ippool_table} \
#	SET FramedIPAddress = FramedIPAddress, \
#	pool_key = '${pool_key}', \
#	expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP), \
#	GatewayIPAddress = '%{DHCP-Gateway-IP-Address}' \
#	OUTPUT INSERTED.FramedIPAddress \
#	FROM ${ippool_table} \
#	WHERE ${ippool_table}.id IN ( \
#		SELECT TOP (1) id FROM ( \
#			(SELECT TOP(1) id, 1 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
#			JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND pool_key = '${pool_key}' \
#			AND dhcpstatus.status IN ('dynamic', 'static')) \
#			UNION \
#			(SELECT TOP(1) id, 2 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
#			JOIN dhcpstatus ON ${ippool_table}.status_id = dhcpstatus.status_id \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
#			AND dhcpstatus.status = 'dynamic' \
#			AND ( pool_key = '%{pool_key}' OR expiry_time < CURRENT_TIMESTAMP )) \
#			UNION \
#			(SELECT TOP(1) id, 3 AS o FROM ${ippool_table} WITH (xlock rowlock readpast) \
#			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 expiry_time) \
#		) AS q ORDER BY q.o \
#	)"

#
#  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 TOP(1) FramedIPAddress 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 \
#			newid() \
#	) \
#	UPDATE cte WITH (rowlock, readpast) \
#	SET FramedIPAddress = FramedIPAddress \
#	OUTPUT INSERTED.FramedIPAddress"

#
#  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 TOP(1) id \
	FROM ${ippool_table} \
	WHERE pool_name='%{control:${pool_name}}'"

#
#  This is the final IP Allocation query, which saves the allocated ip details.
#  Only needed if the initial "find" query is not storing the allocation.
#
#allocate_update = "\
#	UPDATE ${ippool_table} \
#	SET \
#		gateway = '%{DHCP-Gateway-IP-Address}', pool_key = '${pool_key}', \
#		expiry_time = DATEADD(SECOND,${offer_duration},CURRENT_TIMESTAMP) \
#	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 = "\
#	EXEC fr_dhcp_allocate_previous_or_new_framedipaddress \
#		@v_pool_name = '%{control:${pool_name}}', \
#		@v_gateway = '%{DHCP-Gateway-IP-Address}', \
#		@v_pool_key = '${pool_key}', \
#		@v_lease_duration = ${offer_duration}, \
#		@v_requested_address = '%{%{${req_attribute_name}}:-0.0.0.0}' \
#	"
#allocate_update = ""
#allocate_commit = ""


# ****************
# * 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 = '', \
		expiry_time = CURRENT_TIMESTAMP \
	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')"

#
#  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 = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP), \
		counter = counter + 1 \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'"


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

#
#  This query frees an IP address when a DHCP RELEASE packet arrives
#
stop_clear = "\
	UPDATE ${ippool_table} \
	SET \
		gateway = '', \
		pool_key = '', \
		expiry_time = CURRENT_TIMESTAMP \
	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')"

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