| # -*- text -*- |
| # |
| # ippool-dhcp/sqlite/queries.conf -- SQLite queries for rlm_sqlippool |
| # |
| # $Id: 8709a562ee8877f02a43118464371eae101f3fbc $ |
| |
| # |
| # 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 <= datetime(strftime('%%s', 'now') - 1, 'unixepoch') \ |
| 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 < datetime('now')) OR expiry_time IS NULL) \ |
| OR (callingstationid = '%{Calling-Station-Id}') \ |
| AND expiry_time > datetime('now')\ |
| ) \ |
| ORDER BY \ |
| (callingstationid <> '%{Calling-Station-Id}'), \ |
| expiry_time \ |
| LIMIT 1" |
| |
| # |
| # 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 = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ |
| WHERE framedipaddress = '%I' \ |
| AND expiry_time IS NULL" |
| |
| # |
| # The following queries are not used for DHCP IP assignment. |
| # |
| |
| # |
| # This series of queries frees an IP number when an accounting START record arrives |
| # |
| start_update = "\ |
| UPDATE ${ippool_table} \ |
| SET \ |
| expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ |
| 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 = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ |
| 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}'" |