| # -*- text -*- |
| # |
| # ippool-dhcp/mysql/queries.conf -- MySQL queries for rlm_sqlippool |
| # |
| # $Id: ac9476e5091010d39c9212e424efb360a99a1e71 $ |
| |
| # |
| # 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}' |
| # 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}'" |
| |
| # |
| # 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 \ |
| OR UPDATE" |
| |
| # |
| # If you prefer to allocate a random IP address every time, use this query instead |
| # |
| #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" |
| |
| # |
| # 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' AND expiry_time IS NULL" |
| |
| # |
| # 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 = '%{Framed-IP-Address}'" |
| |
| # |
| # 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}' \ |
| AND pool_key = '${pool_key}' \ |
| AND username = '%{User-Name}' \ |
| AND callingstationid = '%{Calling-Station-Id}' \ |
| AND framedipaddress = '%{Framed-IP-Address}'" |
| |
| # |
| # 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}' \ |
| AND pool_key = '${pool_key}' \ |
| AND username = '%{User-Name}' \ |
| AND callingstationid = '%{Calling-Station-Id}' \ |
| AND framedipaddress = '%{Framed-IP-Address}'" |
| |
| # |
| # 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}'" |
| |
| # |
| # 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}'" |