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

#  *****************
#  * 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.
#
#  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_dhcp_allocate_previous_or_new_framedipaddress( \
		'%{control:${pool_name}}', \
		'%{DHCP-Gateway-IP-Address}', \
		'${pool_key}', \
		'${offer_duration}', \
		'%{%{${req_attribute_name}}:-0.0.0.0}' \
	)"
allocate_update = ""
allocate_commit = ""

#
#  If stored procedures are not able to be used, the following queries can
#  be used.
#  Comment out all the above queries and choose the appropriate "allocate_find"
#  to match the desired outcome and also the version of "allocate_update" below.
#

#
#  This sequence of queries allocates an IP address from the Pool
#
#allocate_begin = "BEGIN"


#  Attempt to find the most recent existing IP address for the client
#
#allocate_existing = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND pool_key = '${pool_key}' \
#	AND status IN ('dynamic', 'static') \
#	ORDER BY expiry_time DESC \
#	LIMIT 1 \
#	FOR UPDATE"

#  The same query with SKIP LOCKED - requires PostgreSQL >= 9.5
# allocate_existing = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND pool_key = '${pool_key}' \
#	AND status IN ('dynamic', 'static') \
#	ORDER BY expiry_time DESC \
#	LIMIT 1 \
#	FOR UPDATE SKIP LOCKED"


#
#  Determine whether the requested IP address is available
#
#allocate_requested = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
#	AND status = 'dynamic' \
#	AND expiry_time < 'now'::timestamp(0) \
#	FOR UPDATE"

#  The same query with SKIP LOCKED - requires PostgreSQL >= 9.5
#allocate_requested = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
#	AND status = 'dynamic' \
#	AND expiry_time < 'now'::timestamp(0) \
#	FOR UPDATE SKIP LOCKED"


#
#  If the existing address can't be found this query will be run to
#  find a free address
#
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND expiry_time < 'now'::timestamp(0) \
#	AND status = 'dynamic' \
#	ORDER BY expiry_time \
#	LIMIT 1 \
#	FOR UPDATE"

#  The same query with SKIP LOCKED - requires PostgreSQL >= 9.5
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND expiry_time < 'now'::timestamp(0) \
#	AND status = 'dynamic' \
#	ORDER BY expiry_time \
#	LIMIT 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.
#  Use of either of these next two queries should have the allocate_begin line commented out
#  and allocate_update below un-commented.
#
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' AND expiry_time < 'now'::timestamp(0) \
#	AND status = 'dynamic' \
#	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) \
#	AND status = 'dynamic' \
#	ORDER BY RANDOM() \
#	LIMIT 1 \
#	FOR UPDATE SKIP LOCKED"

#
#  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 \
#		gateway = '%{DHCP-Gateway-IP-Address}', \
#		pool_key = '${pool_key}', \
#		expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval \
#	WHERE framedipaddress = '%I'"


#
#  Alternatively, merge the matching of existing IP and free IP into a single query
#  This version does the update as well - so allocate_begin, allocate_update and
#  allocate_commit should be blank
#
#allocate_begin = ""
#allocate_find = "\
#	WITH found AS ( \
#		WITH existing AS ( \
#			SELECT framedipaddress FROM ${ippool_table} \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND pool_key = '${pool_key}' \
#			ORDER BY expiry_time DESC \
#			LIMIT 1 \
#			FOR UPDATE SKIP LOCKED \
#		), requested AS ( \
#			SELECT framedipaddress FROM ${ippool_table} \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \
#			AND status = 'dynamic' \
#			AND ( pool_key = '${pool_key}' OR expiry_time < 'now'::timestamp(0) ) \
#			FOR UPDATE SKIP LOCKED \
#		), new AS ( \
#			SELECT framedipaddress FROM ${ippool_table} \
#			WHERE pool_name = '%{control:${pool_name}}' \
#			AND expiry_time < 'now'::timestamp(0) \
#			AND status = 'dynamic' \
#			ORDER BY expiry_time \
#			LIMIT 1 \
#			FOR UPDATE SKIP LOCKED \
#		) \
#		SELECT framedipaddress, 1 AS o FROM existing \
#		UNION ALL \
#		SELECT framedipaddress, 2 AS o FROM requested \
#		UNION ALL \
#		SELECT framedipaddress, 3 AS o FROM new \
#		ORDER BY o LIMIT 1 \
#	) \
#	UPDATE ${ippool_table} \
#	SET pool_key = '${pool_key}', \
#	expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval, \
#	gateway = '%{DHCP-Gateway-IP-Address}' \
#	FROM found \
#	WHERE found.framedipaddress = ${ippool_table}.framedipaddress \
#	RETURNING found.framedipaddress"
#allocate_update = ""
#allocate_commit = ""


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


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

#
#  This query revokes any active offers for addresses that a client is not
#  requesting when a DHCP REQUEST packet arrives, i.e, each server (sharing the
#  same database) may have simultaneously offered a unique address.
#
start_update = "\
	UPDATE ${ippool_table} \
	SET \
		gateway = '', \
		pool_key = '', \
		expiry_time = 'now'::timestamp(0) - '1 second'::interval \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \
	AND expiry_time > 'now'::timestamp(0) \
	AND 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 = 'now'::timestamp(0) + '${lease_duration} second'::interval, \
		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 = 'now'::timestamp(0) - '1 second'::interval \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{DHCP-Client-IP-Address}' \
	AND 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 = 'declined' \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{DHCP-Requested-IP-Address}'"