1 files changed, 66 insertions, 0 deletions
diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql
index f2daa21..bf3225b 100644
--- a/notes/projects/radius/sql/ippool-queries.sql
+++ b/notes/projects/radius/sql/ippool-queries.sql
@@ -10,6 +10,72 @@ SET ROLE radius_user;
 /*
+ *  find user's previous address if available
+ *
+ *  This query is provided as an example, production systems should use the
+ *  function ip_autoassign().
+ */
+\set myUsername    'jdoe8@example.net'
+\set myNasName     'br1.sol'
+\set myPoolName    'DHCP'
+SELECT              ip.address       AS    address,
+                    ip.modified      AS    modified,
+                    ip.username      AS    username,
+                    ip.acctsession   AS    acctsession,
+                    pool.name        AS    pool,
+                    region.name      AS    region
+   FROM             ip
+   INNER JOIN       netblock
+      ON            netblock.network >>=   ip.address
+   INNER JOIN       region
+      ON            region.id        =     netblock.regionId
+   INNER JOIN       pool
+      ON            pool.id          =     netblock.poolId
+   INNER JOIN       nas
+      ON            nas.regionId     =     region.id
+   WHERE            ip.nasId         IS    NULL
+      AND           ip.username      LIKE  lower( :'myUsername' )
+      AND           nas.name         LIKE  lower( :'myNasName' )
+      AND           pool.name        LIKE  lower( :'myPoolName' )
+      AND           netblock.active  =     TRUE
+   ORDER BY         ip.modified      DESC,
+                    ip.address       ASC
+   LIMIT            1;
+/*
+ *  find next available IP address for a given NAS and pool
+ *
+ *  This query is provided as an example, production systems should use the
+ *  function ip_autoassign().
+ */
+ \set myNasName     'br1.sol'
+ \set myPoolName    'DHCP'
+SELECT              ip.address       AS    address,
+                    ip.modified      AS    modified,
+                    ip.username      AS    username,
+                    ip.acctsession   AS    acctsession,
+                    pool.name        AS    pool,
+                    region.name      AS    region
+   FROM             ip
+   INNER JOIN       netblock
+      ON            netblock.network >>=   ip.address
+   INNER JOIN       region
+      ON            region.id        =     netblock.regionId
+   INNER JOIN       pool
+      ON            pool.id          =     netblock.poolId
+   INNER JOIN       nas
+      ON            nas.regionId     =     region.id
+   WHERE            ip.nasId         IS    NULL
+      AND           nas.name         LIKE  lower( :'myNasName' )
+      AND           pool.name        LIKE  lower( :'myPoolName' )
+      AND           netblock.active  =     TRUE
+   ORDER BY         ip.modified      ASC,
+                    ip.address       ASC
+   LIMIT            1;
+/*
 *  list all IP addresses with referenced/inferred data
 *
 *  This query will return a potentialy large dataset which makes it

diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql index f2daa21..bf3225b 100644 --- a/notes/projects/radius/sql/ippool-queries.sql +++ b/notes/projects/radius/sql/ippool-queries.sql
@@ -10,6 +10,72 @@ SET ROLE radius_user;
10		10
11		11
12	/*	12	/*
		13	* find user's previous address if available
		14	*
		15	* This query is provided as an example, production systems should use the
		16	* function ip_autoassign().
		17	*/
		18	\set myUsername 'jdoe8@example.net'
		19	\set myNasName 'br1.sol'
		20	\set myPoolName 'DHCP'
		21	SELECT ip.address AS address,
		22	ip.modified AS modified,
		23	ip.username AS username,
		24	ip.acctsession AS acctsession,
		25	pool.name AS pool,
		26	region.name AS region
		27	FROM ip
		28	INNER JOIN netblock
		29	ON netblock.network >>= ip.address
		30	INNER JOIN region
		31	ON region.id = netblock.regionId
		32	INNER JOIN pool
		33	ON pool.id = netblock.poolId
		34	INNER JOIN nas
		35	ON nas.regionId = region.id
		36	WHERE ip.nasId IS NULL
		37	AND ip.username LIKE lower( :'myUsername' )
		38	AND nas.name LIKE lower( :'myNasName' )
		39	AND pool.name LIKE lower( :'myPoolName' )
		40	AND netblock.active = TRUE
		41	ORDER BY ip.modified DESC,
		42	ip.address ASC
		43	LIMIT 1;
		44
		45
		46	/*
		47	* find next available IP address for a given NAS and pool
		48	*
		49	* This query is provided as an example, production systems should use the
		50	* function ip_autoassign().
		51	*/
		52	\set myNasName 'br1.sol'
		53	\set myPoolName 'DHCP'
		54	SELECT ip.address AS address,
		55	ip.modified AS modified,
		56	ip.username AS username,
		57	ip.acctsession AS acctsession,
		58	pool.name AS pool,
		59	region.name AS region
		60	FROM ip
		61	INNER JOIN netblock
		62	ON netblock.network >>= ip.address
		63	INNER JOIN region
		64	ON region.id = netblock.regionId
		65	INNER JOIN pool
		66	ON pool.id = netblock.poolId
		67	INNER JOIN nas
		68	ON nas.regionId = region.id
		69	WHERE ip.nasId IS NULL
		70	AND nas.name LIKE lower( :'myNasName' )
		71	AND pool.name LIKE lower( :'myPoolName' )
		72	AND netblock.active = TRUE
		73	ORDER BY ip.modified ASC,
		74	ip.address ASC
		75	LIMIT 1;
		76
		77
		78	/*
13	* list all IP addresses with referenced/inferred data	79	* list all IP addresses with referenced/inferred data
14	*	80	*
15	* This query will return a potentialy large dataset which makes it	81	* This query will return a potentialy large dataset which makes it