/*
 *  IP Engineering RADIUS Servers
 *
 *  ippool-demo.sql - inserts demo data into  RADIUS IP Pools database
 *
 */


SET ROLE radius_user;


/*
 *  list all IP addresses with referenced/inferred data
 *
 *  This query will return a potentialy large dataset which makes it
 *  unsuitable for a function or stored procedure.  The implementation of
 *  `RETURN QUERY` in PostgreSQL 11 stores the entire result set before
 *  returning from a function.  If the dataset is very large, the result
 *  set will be written to disk to avoid memory exchaustion. This will lead
 *  to performance issues.
 *
 *  Additionally, the following query is very generic and most likely an
 *  administrator will need to customize the query to obtain the needed
 *  result set for a given need.
 */
 --
 -- Method 1:
SELECT DISTINCT      pool.name         AS poolName,
                     region.name       AS regionName,
                     ip.address        AS address,
                     TRUE              = ALL
                     (
                       SELECT          nb2.active
                          FROM         netblock   AS  nb2
                          WHERE        ip.address <<= nb2.network
                     )                 AS active,
                     (
                       nasId           IS NOT NULL
                     )                 AS assigned,
                     ip.modified       AS modified,
                     nas.name          AS nasName,
                     ip.username       AS username,
                     ip.acctSession    AS acctSession
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   INNER JOIN        pool
      ON             netblock.poolId   =   pool.id
   LEFT OUTER JOIN   region
      ON             netblock.regionId =   region.id
   LEFT OUTER JOIN   nas
      ON             ip.nasId          =   nas.id
   ORDER BY          address;
--
-- Method 2:
SELECT DISTINCT      pool.name         AS poolName,
                     region.name       AS regionName,
                     ip.address        AS address,
                     NOT EXISTS
                     (
                       SELECT          *
                          FROM         netblock   AS  nb2
                          WHERE        ip.address <<= nb2.network
                             AND NOT   nb2.active
                     )                 AS active,
                     (
                       nasId           IS NOT NULL
                     )                 AS assigned,
                     ip.modified       AS modified,
                     nas.name          AS nasName,
                     ip.username       AS username,
                     ip.acctSession    AS acctSession
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   INNER JOIN        pool
      ON             netblock.poolId   =   pool.id
   LEFT OUTER JOIN   region
      ON             netblock.regionId =   region.id
   LEFT OUTER JOIN   nas
      ON             ip.nasId          =   nas.id
   ORDER BY          address;


/*
 *  list all inactive IP addresses
 */
SELECT DISTINCT      ip.address        AS  ipAddress,
                     netblock.network  AS  network,
                     netblock.active   AS  active
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   WHERE             netblock.active   =   FALSE
   ORDER BY          ipAddress;


/*
 *  list all active IP addresses
 */
SELECT DISTINCT      netblock.regionId AS  regionId,
                     ip.address        AS  ipAddress,
                     netblock.active   AS  active
   FROM              ip
   INNER JOIN        netblock
      ON             ip.address        <<= netblock.network
   WHERE             netblock.active   =   TRUE
      AND            ip.address             NOT IN
                     (
                       SELECT          address AS ipAddress
                          FROM         ip
                          INNER JOIN   netblock
                             ON        ip.address          <<= netblock.network
                                AND    netblock.active     =   FALSE
                     )
   ORDER BY          ipAddress;


/* end of sql */