From 6c6c00ca209e8d5971b0aee817606af090f0bd2c Mon Sep 17 00:00:00 2001 From: "David M. Syzdek" Date: Thu, 23 May 2019 22:10:28 -0800 Subject: updating query examples --- notes/projects/radius/sql/ippool-queries.sql | 28 ++++++++++++++-------------- 1 file changed, 14 insertions(+), 14 deletions(-) (limited to 'notes') diff --git a/notes/projects/radius/sql/ippool-queries.sql b/notes/projects/radius/sql/ippool-queries.sql index 9a82fc6..d5ad3b9 100644 --- a/notes/projects/radius/sql/ippool-queries.sql +++ b/notes/projects/radius/sql/ippool-queries.sql @@ -36,7 +36,7 @@ SELECT ip.address AS address, AND ip.username LIKE lower( :'myUsername' ) AND nas.name LIKE lower( :'myNasName' ) AND pool.name LIKE lower( :'myPoolName' ) - AND netblock.assignable = TRUE + AND netblock.busyout = FALSE ORDER BY ip.modified DESC, ip.address ASC LIMIT 1; @@ -58,7 +58,7 @@ SELECT ip.address AS address, FROM ip INNER JOIN netblock ON netblock.network >>= ip.address - AND netblock.assignable IS TRUE + AND netblock.busyout IS FALSE INNER JOIN region ON region.id = netblock.regionId INNER JOIN pool @@ -68,7 +68,7 @@ SELECT ip.address AS address, WHERE ip.nasId IS NULL AND nas.name LIKE lower( :'myNasName' ) AND pool.name LIKE lower( :'myPoolName' ) - AND netblock.assignable = TRUE + AND netblock.busyout = FALSE ORDER BY ip.modified ASC, ip.address ASC LIMIT 1; @@ -93,12 +93,12 @@ SELECT ip.address AS address, SELECT DISTINCT pool.name AS poolName, region.name AS regionName, ip.address AS address, - TRUE = ALL + TRUE = SOME ( - SELECT nb2.assignable + SELECT nb2.busyout FROM netblock AS nb2 WHERE ip.address <<= nb2.network - ) AS active, + ) AS busyout, ( nasId IS NOT NULL ) AS assigned, @@ -120,13 +120,13 @@ SELECT DISTINCT pool.name AS poolName, SELECT DISTINCT pool.name AS poolName, region.name AS regionName, ip.address AS address, - NOT EXISTS + EXISTS ( SELECT * FROM netblock AS nb2 WHERE ip.address <<= nb2.network - AND NOT nb2.assignable - ) AS active, + AND nb2.busyout + ) AS busyout, ( nasId IS NOT NULL ) AS assigned, @@ -150,11 +150,11 @@ SELECT DISTINCT pool.name AS poolName, */ SELECT DISTINCT ip.address AS ipAddress, netblock.network AS network, - netblock.assignable AS active + netblock.busyout AS active FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - WHERE netblock.assignable = FALSE + WHERE netblock.busyout = TRUE ORDER BY ipAddress; @@ -163,18 +163,18 @@ SELECT DISTINCT ip.address AS ipAddress, */ SELECT DISTINCT netblock.regionId AS regionId, ip.address AS ipAddress, - netblock.assignable AS active + netblock.busyout AS busyout FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - WHERE netblock.assignable = TRUE + WHERE netblock.busyout = FALSE AND ip.address NOT IN ( SELECT address AS ipAddress FROM ip INNER JOIN netblock ON ip.address <<= netblock.network - AND netblock.assignable = FALSE + AND netblock.busyout = TRUE ) ORDER BY ipAddress; -- cgit v1.2.3