# -*- text -*-
#
#  ippool-dhcp/sqlite/queries.conf -- SQLite queries for rlm_sqlippool
#
#  $Id$

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

#
#  SQLite does not implement SELECT FOR UPDATE which is normally used to place
#  an exclusive lock over rows to prevent the same address from being
#  concurrently selected for allocation to multiple users.
#
#  The most granular read-blocking lock that SQLite has is an exclusive lock
#  over the database, so that's what we use. All locking in SQLite is performed
#  over the entire database and we perform a row update for any IP that we
#  allocate, requiring an exclusive lock. Taking the exclusive lock from the
#  start of the transaction (even if it were not required to guard the SELECT)
#  is actually quicker than if we deferred it causing SQLite to "upgrade" the
#  automatic shared lock for the transaction to an exclusive lock for the
#  subsequent UPDATE.
#
allocate_begin = "BEGIN EXCLUSIVE"
allocate_commit = "COMMIT"

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

#
#  Determine whether the requested IP address is available
#
allocate_requested = "\
	SELECT framedipaddress \
	FROM ${ippool_table} \
	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 status = 'dynamic' \
	AND expiry_time < datetime('now')"

#
#  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} \
	JOIN dhcpstatus \
	ON ${ippool_table}.status_id = dhcpstatus.status_id \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND expiry_time < datetime('now') \
	AND status = 'dynamic' \
	ORDER BY expiry_time LIMIT 1"

#
#  This series of queries allocates an IP address
#
#  Either pull the most recent allocated IP for this client or the
#  oldest expired one.  The first sub query returns the most recent
#  lease for the client (if there is one), the second returns the
#  oldest expired one.
#  Sorting the result by expiry_time DESC will return the client specific
#  IP if it exists, otherwise an expired one.
#
#allocate_find = "\
#	SELECT framedipaddress, 1 AS o \
#	FROM ( \
#		SELECT framedipaddress \
#		FROM ${ippool_table} \
#		JOIN dhcpstatus \
#		ON ${ippool_table}.status_id = dhcpstatus.status_id \
#		WHERE pool_name = '%{control:${pool_name}}' \
#		AND pool_key = '${pool_key}' \
#		AND status IN ('dynamic', 'static') \
#		ORDER BY expiry_time DESC \
#		LIMIT 1 \
#	) UNION \
#	SELECT framedipaddress, 2 AS o \
#	FROM ( \
#		SELECT framedipaddress \
#		FROM ${ippool_table} \
#		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 status = 'dynamic' \
#		AND ( pool_key = '${pool_key}' OR expiry_time < datetime('now') ) \
#	) UNION \
#	SELECT framedipaddress, 3 AS o \
#	FROM ( \
#		SELECT framedipaddress \
#		FROM ${ippool_table} \
#		JOIN dhcpstatus \
#		ON ${ippool_table}.status_id = dhcpstatus.status_id \
#		WHERE pool_name = '%{control:${pool_name}}' \
#		AND expiry_time < datetime('now') \
#		AND status = 'dynamic' \
#		ORDER BY expiry_time LIMIT 1 \
#	) \
#	ORDER BY o \
#	LIMIT 1"

#
#   If you prefer to allocate a random IP address every time, i
#   use this query instead
#   Note: This is very slow if you have a lot of free IPs.
#

#allocate_find = "\
#	SELECT framedipaddress \
#	FROM ${ippool_table} \
#	JOIN dhcpstatus \
#	ON ${ippool_table}.status_id = dhcpstatus.status_id \
# 	WHERE pool_name = '%{control:${pool_name}}' \
#	AND expiry_time < datetime('now') \
#	AND status = 'dynamic' \
#	ORDER BY RAND() \


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

#
#  This is the final IP Allocation query, which saves the allocated ip details
#
allocate_update = "\
	UPDATE ${ippool_table} \
	SET \
		gateway = '%{DHCP-Gateway-IP-Address}', \
		pool_key = '${pool_key}', \
		expiry_time = datetime(strftime('%%s', 'now') + ${offer_duration}, 'unixepoch') \
	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 = '', \
		expiry_time = datetime('now') \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \
	AND expiry_time > datetime('now') \
	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 = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch'), \
		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 = datetime('now') \
	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}'"