From 38f8f812456f6aef3ff42233585cd0838c81ecd7 Mon Sep 17 00:00:00 2001
From: pnixon <pnixon>
Date: Tue, 8 May 2007 07:00:35 +0000
Subject: [PATCH] Split queries into dialect specific files and add mysql
 support

---
 raddb/sql/mysql-ippool-dialup.conf      | 88 +++++++++++++++++++++++++++++++++
 raddb/sql/postgresql-ippool-dialup.conf | 80 ++++++++++++++++++++++++++++++
 raddb/sqlippool.conf                    | 82 +++---------------------------
 3 files changed, 175 insertions(+), 75 deletions(-)
 create mode 100644 raddb/sql/mysql-ippool-dialup.conf
 create mode 100644 raddb/sql/postgresql-ippool-dialup.conf

diff --git a/raddb/sql/mysql-ippool-dialup.conf b/raddb/sql/mysql-ippool-dialup.conf
new file mode 100644
index 0000000..3d799c4
--- /dev/null
+++ b/raddb/sql/mysql-ippool-dialup.conf
@@ -0,0 +1,88 @@
+#  $Id$
+#
+# FreeRADIUS rlm_sqlippool SQL Queries for the MySQL Dialect 
+
+ #
+ # This series of queries allocates an IP address
+ #
+ allocate-clear = "UPDATE radippool \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  WHERE pool_key = '${pool-key}'"
+
+ # note the ORDER BY clause of next query, it'll try to allocate IPs
+ # like Cisco internal pools do - it _trys_ to allocate the same IP-address
+ # which user had last session...
+ allocate-find = "SELECT FramedIPAddress FROM radippool \
+  WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < NOW() \
+  ORDER BY pool_name, (UserName <> '%{User-Name}'), (CallingStationId <>
+'%{Calling-Station-Id}'), expiry_time \
+  LIMIT 1 \
+  FOR UPDATE"
+
+ allocate-update = "UPDATE radippool \
+  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 = '%{Framed-IP-Address}'"
+
+
+
+ #
+ # This series of queries frees an IP number when an accounting
+ # START record arrives
+ #
+ start-update = "UPDATE radippool \
+  SET expiry_time = NOW() + INTERVAL %J SECOND \
+  WHERE NASIPAddress = '%n' AND pool_key = '${pool-key}' AND pool_name =
+'%P'"
+
+ #
+ # This series of queries frees an IP number when an accounting
+ # STOP record arrives
+ #
+ stop-clear = "UPDATE radippool \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 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
+ # ALIVE record arrives
+ #
+ alive-update = "UPDATE radippool \
+  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 radippool \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  WHERE NASIPAddress = '%{NAS-IP-Address}' 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 OFF record arrives
+ #
+ off-clear = "UPDATE radippool \
+  SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
+  expiry_time = NOW() - INTERVAL 1 SECOND \
+  WHERE NASIPAddress = '%{NAS-IP-Address}' AND UserName = '%{User-Name}' \
+  AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress =
+'%{Framed-IP-Address}'"
+
diff --git a/raddb/sql/postgresql-ippool-dialup.conf b/raddb/sql/postgresql-ippool-dialup.conf
new file mode 100644
index 0000000..ed3fcbb
--- /dev/null
+++ b/raddb/sql/postgresql-ippool-dialup.conf
@@ -0,0 +1,80 @@
+#  $Id$
+#
+# FreeRADIUS rlm_sqlippool SQL Queries for the PostgreSQL Dialect
+
+ ## This series of queries allocates an IP address
+ allocate-clear = "UPDATE ${ippool_table} \
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
+  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
+  WHERE pool_key = '${pool-key}'"
+
+ ## 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 = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
+  ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
+  LIMIT 1 \
+  FOR 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 = '%P' AND expiry_time < 'now'::timestamp(0) \
+ # ORDER BY RANDOM() \
+ # 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='%{check:Pool-Name}' LIMIT 1"
+
+ 
+ allocate-update = "UPDATE ${ippool_table} \
+  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
+  callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
+  expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
+  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'::timestamp(0) + '${lease-duration} second'::interval \
+  WHERE nasipaddress = '%{NAS-IP-Address}' AND  pool_key = '${pool-key}'"
+
+ ## 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 = '', \
+  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
+  WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-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'::timestamp(0) + '${lease-duration} seconds'::interval \
+  WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-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 = '', \
+  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
+  WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-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 OFF record arrives
+ off-clear = "UPDATE ${ippool_table} \
+  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
+  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
+  WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
+  AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
+
diff --git a/raddb/sqlippool.conf b/raddb/sqlippool.conf
index fdc4a83..8f47eb3 100644
--- a/raddb/sqlippool.conf
+++ b/raddb/sqlippool.conf
@@ -38,80 +38,12 @@ sqlippool {
  sqlippool_log_nopool = "No Pool-Name defined \
   (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
 
- ## This series of queries allocates an IP address
- allocate-clear = "UPDATE ${ippool_table} \
-  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
-  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
-  WHERE pool_key = '${pool-key}'"
-
- ## 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 = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
-  ORDER BY (username <> '%{SQL-User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
-  LIMIT 1 \
-  FOR 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 = '%P' AND expiry_time < 'now'::timestamp(0) \
- # ORDER BY RANDOM() \
- # 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='%{check:Pool-Name}' LIMIT 1"
-
- 
- allocate-update = "UPDATE ${ippool_table} \
-  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
-  callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \
-  expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
-  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'::timestamp(0) + '${lease-duration} second'::interval \
-  WHERE nasipaddress = '%{NAS-IP-Address}' AND  pool_key = '${pool-key}'"
-
- ## 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 = '', \
-  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
-  WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-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'::timestamp(0) + '${lease-duration} seconds'::interval \
-  WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-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 = '', \
-  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
-  WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-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 OFF record arrives
- off-clear = "UPDATE ${ippool_table} \
-  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
-  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
-  WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{SQL-User-Name}' \
-  AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
+
+
+ ## Uncomment the appropriate config file for your SQL dialect
+
+ # $INCLUDE ${confdir}/sql/mysql-dialup.conf
+ $INCLUDE ${confdir}/sql/postgresql-ippool-dialup.conf
+
 
 }
-- 
2.1.4