diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/sqlite')
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/sqlite/queries.conf | 236 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/sqlite/schema.sql | 25 |
2 files changed, 261 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool-dhcp/sqlite/queries.conf b/raddb/mods-config/sql/ippool-dhcp/sqlite/queries.conf new file mode 100644 index 0000000..d99e09b --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/sqlite/queries.conf @@ -0,0 +1,236 @@ +# -*- 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}'" + diff --git a/raddb/mods-config/sql/ippool-dhcp/sqlite/schema.sql b/raddb/mods-config/sql/ippool-dhcp/sqlite/schema.sql new file mode 100644 index 0000000..339d58d --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/sqlite/schema.sql @@ -0,0 +1,25 @@ +-- +-- Table structure for table 'dhcpippool' +-- +CREATE TABLE dhcpstatus ( + status_id int PRIMARY KEY, + status varchar(10) NOT NULL +); + +INSERT INTO dhcpstatus (status_id, status) VALUES (1, 'dynamic'), (2, 'static'), (3, 'declined'), (4, 'disabled'); + +CREATE TABLE dhcpippool ( + id int(11) PRIMARY KEY, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL default '', + pool_key varchar(30) NOT NULL default '', + gateway varchar(15) NOT NULL default '', + expiry_time DATETIME NOT NULL default (DATETIME('now')), + status_id int NOT NULL default 1, + counter int NOT NULL default 0, + FOREIGN KEY(status_id) REFERENCES dhcpstatus(status_id) +); + +CREATE INDEX dhcpippool_poolname_expire ON dhcpippool(pool_name, expiry_time); +CREATE INDEX dhcpippool_framedipaddress ON dhcpippool(framedipaddress); +CREATE INDEX dhcpippool_poolname_poolkey_ipaddress ON dhcpippool(pool_name, pool_key, framedipaddress); |