# -*- text -*- # # ippool/mysql/queries.conf -- MySQL queries for rlm_sqlippool # # $Id: 3e8b85848bbe71109830f551b7a305267e0176c2 $ # # This series of queries allocates an IP address # #allocate_clear = "\ # UPDATE ${ippool_table} \ # SET \ # nasipaddress = '', \ # pool_key = 0, \ # callingstationid = '', \ # username = '', \ # expiry_time = NULL \ # WHERE pool_key = '${pool_key}'" # # This series of queries allocates an IP address # (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}:-%{Nas-IPv6-Address}}' # from the WHERE clause) # allocate_clear = "\ UPDATE ${ippool_table} \ SET \ nasipaddress = '', \ pool_key = 0, \ callingstationid = '', \ username = '', \ expiry_time = NULL \ WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \ AND nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" # # The ORDER BY clause of this query tries to allocate the same IP-address # which user had last session... # allocate_find = "\ SELECT framedipaddress FROM ${ippool_table} \ WHERE pool_name = '%{control:${pool_name}}' \ AND (expiry_time < NOW() OR expiry_time IS NULL) \ ORDER BY \ (username <> '%{User-Name}'), \ (callingstationid <> '%{Calling-Station-Id}'), \ expiry_time \ LIMIT 1 \ FOR UPDATE" # # The above query again, but with SKIP LOCKED. This requires MySQL >= 8.0.1, # and InnoDB. # #allocate_find = "\ # SELECT framedipaddress FROM ${ippool_table} \ # WHERE pool_name = '%{control:${pool_name}}' \ # AND (expiry_time < NOW() OR expiry_time IS NULL) \ # ORDER BY \ # (username <> '%{User-Name}'), \ # (callingstationid <> '%{Calling-Station-Id}'), \ # 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. # #allocate_find = "\ # SELECT framedipaddress FROM ${ippool_table} \ # WHERE pool_name = '%{control:${pool_name}}' \ # AND expiry_time IS NULL \ # ORDER BY \ # RAND() \ # LIMIT 1 \ # FOR UPDATE" # # The above query again, but with SKIP LOCKED. This requires MySQL >= 8.0.1, # and InnoDB. # #allocate_find = "\ # SELECT framedipaddress FROM ${ippool_table} \ # WHERE pool_name = '%{control:${pool_name}}' \ # AND expiry_time IS NULL \ # ORDER BY \ # RAND() \ # LIMIT 1 \ # FOR UPDATE SKIP LOCKED" # # 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 \ nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ callingstationid = '%{Calling-Station-Id}', \ username = '%{User-Name}', expiry_time = NOW() + INTERVAL ${lease_duration} SECOND \ WHERE framedipaddress = '%I' # # This series of queries frees an IP number when an accounting START record arrives. # start_update = "\ UPDATE ${ippool_table} \ SET \ expiry_time = NOW() + INTERVAL ${lease_duration} SECOND \ WHERE nasipaddress = '%{NAS-IP-Address}' \ AND pool_key = '${pool_key}' \ AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' \ AND framedipaddress = '%{${attribute_name}}'" # # This series of queries frees an IP number when an accounting STOP record arrives. # stop_clear = "\ UPDATE ${ippool_table} \ SET \ nasipaddress = '', \ pool_key = 0, \ callingstationid = '', \ username = '', \ expiry_time = NULL \ WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ AND pool_key = '${pool_key}' \ AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' \ AND framedipaddress = '%{${attribute_name}}'" # # This series of queries frees an IP number when an accounting ALIVE record arrives. # alive_update = "\ UPDATE ${ippool_table} \ SET \ expiry_time = NOW() + INTERVAL ${lease_duration} SECOND \ WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ AND pool_key = '${pool_key}' \ AND username = '%{User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' \ AND framedipaddress = '%{${attribute_name}}'" # # This series of queries frees the IP numbers allocate to a # NAS when an accounting ON record arrives # on_clear = "\ UPDATE ${ippool_table} \ SET \ nasipaddress = '', \ pool_key = 0, \ callingstationid = '', \ username = '', \ expiry_time = NULL \ WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" # # This series of queries frees the IP numbers allocate to a # NAS when an accounting OFF record arrives # off_clear = "\ UPDATE ${ippool_table} \ SET \ nasipaddress = '', \ pool_key = 0, \ callingstationid = '', \ username = '', \ expiry_time = NULL \ WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"