src/test/setup/radius-config/freeradius/mods-config/sql/ippool-dhcp/oracle/queries.conf

# -*- text -*-
#
#  ippool-dhcp/oracle/queries.conf -- Oracle queries for dhcp-ippool
#
#  $id: 416d59802a1321c16b936bb5e63c288ca3634bcd $

#
#  "START TRANSACTION" not required with Oracle
#
allocate_begin = ""
start_begin = ""
alive_begin = ""
stop_begin = ""
on_begin = ""
off_begin = ""

#
#  This query allocates an IP address from the Pool
#  It query tries to allocate to the user
#  either the same IP-address that they had last session
#  or the IP which has been unused for the longest period of time
#
allocate_find = "\
	WITH POOLS AS (\
		SELECT * \
		FROM ${ippool_table} \
		WHERE pool_name = '%{control:Pool-Name}' \
		AND (\
			pool_key = '${pool_key}' \
			OR expiry_time = (\
				SELECT MIN(expiry_time) \
				FROM ${ippool_table} \
				WHERE pool_name = '%{control:Pool-Name}' \
				AND expiry_time < CURRENT_TIMESTAMP AND pool_key != '${pool_key}'\
			)\
		)\
	) \
	SELECT framedipaddress \
	FROM (\
		SELECT framedipaddress \
		FROM POOLS \
		WHERE pool_key = '${pool_key}' \
		OR (\
			NOT EXISTS (\
				SELECT 1 \
				FROM POOLS \
				WHERE pool_key = '${pool_key}'\
			)\
		)\
	) WHERE ROWNUM = 1 FOR UPDATE"

#
#  This function is available if you want to use multiple pools
#
#allocate_find = "\
	SELECT msqlippool('%{SQL-User-Name}','%{control:Pool-Name}') \
	FROM dual"

#
#  If you prefer to allocate a random IP address every time, use this query instead
#
#allocate_find = "\
#	SELECT framedipaddress \
#	FROM ${ippool_table}\
#	WHERE framedipaddress = (\
#		SELECT framedipaddress \
#		FROM (\
#			SELECT framedipaddress \
#			FROM ${ippool_table} \
#			WHERE pool_name = '%{control:Pool-Name}' \
#			AND expiry_time < CURRENT_TIMESTAMP \
#			ORDER BY DBMS_RANDOM.VALUE\
#		) \
#		WHERE ROWNUM = 1\
#	) \
#	FOR UPDATE"

#
#  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}', \
		username = '%{SQL-User-Name}', \
		expiry_time = CURRENT_TIMESTAMP + INTERVAL '${lease_duration}' SECOND(1) \
	WHERE framedipaddress = '%I'"

#
#  This query frees the IP address assigned to "pool_key" when a new request
#  comes in for the same "pool_key". This means that either you are losing
#  accounting Stop records or you use Calling-Station-id instead of NAS-Port
#  as your "pool_key" and your users are able to reconnect before your NAS
#  has timed out their previous session. (Generally on wireless networks)
#  (Note: If your pool_key is set to Calling-Station-id and not NAS-Port
#  then you may wish to delete the "AND nasipaddress = '%{NAS-IP-Address}'
#  from the WHERE clause)
#
allocate_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
	WHERE pool_key = '${pool_key}'"

#
#  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_name = '%{control:Pool-Name}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{Framed-IP-Address}'"

#
#  This query frees an IP address when an accounting
#  STOP record arrives
#
stop_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = CURRENT_TIMESTAMP - INTERVAL '1' SECOND(1) \
	WHERE pool_key = '${pool_key}'"

#
#  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 pool_key = '${pool_key}' \
	AND pool_name = '%{control:Pool-Name}' \
	AND framedipaddress = '%{Framed-IP-Address}'"

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

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