Updating documentation

2 files changed, 215 insertions, 17 deletions

diff --git a/notes/projects/radius/sql/README.ippool.md b/notes/projects/radius/sql/README.ippool.md
index b463df8..d6ed50f 100644
--- a/notes/projects/radius/sql/README.ippool.md
+++ b/notes/projects/radius/sql/README.ippool.md
@@ -5,7 +5,7 @@ IP Engineering RADIUS IP Pool Database
 Objectives
 ----------
 
-   * Psuedo static IP addresses
+   * Psuedo static IP addresses (i.e. "sticky" IP addresses)
    * Per NAS Preference for geographic regions
    * Ability to deavtivate/busy out specific netblocks
 
@@ -77,10 +77,88 @@ The following PostgreSQL (psql) commands will purge RADIUS roles from Postgresql
       DROP ROLE radius;
 
 
-Network Block Management
-------------------------
+Auditing Reports
+----------------
 
+The following displays IP usage by IP pool:
+
+      SELECT * FROM pool_audit()     ORDER BY poolname, regionType;   
+
+The following displays IP usage by IP pool and region:
+
+      SELECT * FROM region_audit()   ORDER BY poolName, regionName;
+
+The following displays IP usage by defined netblocks:
+
+      SELECT * FROM netblock_audit() ORDER BY poolName, network;
+
+The following displays the state of each IP address:
+
+      SELECT * FROM ip_audit()       ORDER BY ipAddress;
 
 
 IP Address Assignments
 ----------------------
+
+IP addresses can be manually assigned or auto-assigned based upon the NAS and
+pool type specified.
+
+The following is an example of manually specifing an IP address assignment:
+
+      SELECT ip_assign
+             ( assignIp      := '209.193.4.8/32',
+               assignNas     := 'br1.sol',
+               assignUser    := 'jdoe8@example.net'
+             );
+
+The above assignment will fail if a netblock containing '209.193.4.8/32' has
+been marked as 'busyout'.  A assignment to a busy netblock can be forced using
+the 'assignForce' option:
+
+      SELECT ip_assign
+             ( assignIp      := '209.193.4.3/32',
+               assignNas     := 'br1.sol',
+               assignUser    := 'busy1@example.net',
+               assignForce   := TRUE
+             );
+
+The following is an example of auto-assigning an IP address based upon a client
+session's NAS and pool type:
+
+      SELECT ip_autoassign
+             ( assignUser    := 'autoassign1@example.net',
+               assignNas     := 'br1.sol',
+               assignPool    := 'dhcp'
+             );
+
+By default, `ip_autoassign()` will attempt to assign "sticky" IP addresses to a
+session. A sticky address is a best effort attempt to re-assign the IP address
+previously used by the subscriber.  Sticky IP address assignments use the
+following logic to assign an IP address to a user session:
+
+   * Attempt to assign regional pool address which was last used by subscriber.
+   * Attempt to assign regional pool address which has been idle the longest.
+   * Attempt to assign non-regional pool address which was last used by subscriber.
+   * Attempt to assign non-regional pool address which has been idle the longest.
+   * Fail address assignment.
+
+Dynamic IP addresses can be requested by passing 'assignPrevIp' with a value of
+FALSE to `ip_autoassign()`:
+
+      SELECT ip_autoassign
+             ( assignUser    := 'autoassign2@example.net',
+               assignNas     := 'br1.sol',
+               assignPool    := 'dhcp',
+               assignPrevIp  := FALSE
+             );
+
+When assigning auto-assigning dynamic IP addresses, the following logic is used:
+
+   * Attempt to assign regional pool address which has been idle the longest.
+   * Attempt to assign non-regional pool address which has been idle the longest.
+   * Fail address assignment.
+
+Both manual and auto-assigned IP addresses are freed using `ip_release()`:
+
+      SELECT ip_release( '209.193.4.3' );
+
diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index f7cc8c5..3bbabdf 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -223,7 +223,35 @@ $$;
  *
  *  % SELECT * FROM ip_audit();
  *
- *  Returns table of ip
+ *  Returns table with the following columns:
+ *
+ *  poolName
+ *  : name of pool which owns IP address
+ *
+ *  regionname
+ *  : name of region which owns IP address or NULL if not assigned to a region
+ *
+ *  ipAddress
+ *  : the IP address of the current record
+ *
+ *  busyout
+ *  : if TRUE, IP address cannot be assigned without being forced
+ *
+ *  assigned
+ *  : if true, IP address is assigend to a session.  If false, IP address is
+ *  : available
+ *
+ *  modified
+ *  : time stamp of last assignment or release
+ *
+ *  nasName
+ *  : NAS name of current assignment, NULL if IP address is not assigned to a
+ *  : session
+ *
+ *  username
+ *  : username of current subscriber if IP address is assigned or username of
+ *  : prevsious subscriber if IP address is not currently assigned.
+ *
  */
 CREATE OR REPLACE FUNCTION ip_audit
 (
@@ -447,9 +475,16 @@ $$;
 
 
 /*
- *  updates timestamp of addresses
+ *  Releases IP address from current assignment
+ *
+ *  % SELECT ip_release(
+ *       assignIp   := '209.193.4.1'
+ *    )
+ *
+ *  assignIp
+ *  : IP address to release
  *
- *  returns trigger
+ *  returns TRUE if sucessful and FALSE if unsuccessful
  */
 CREATE OR REPLACE FUNCTION ip_release
 (
@@ -631,7 +666,36 @@ $$;
  *
  *  % SELECT * FROM netblock_audit();
  *
- *  Returns table of netblocks
+ *  Returns table with the following columns:
+ *
+ *  poolName
+ *  : name of pool which owns IP address
+ *
+ *  regionname
+ *  : name of region which owns IP address or NULL if not assigned to a region
+ *
+ *  network
+ *  : network address and CIDR of netblock
+ *
+ *  busyout
+ *  : if TRUE, IP address cannot be assigned without being forced
+ *
+ *  ipAssigned
+ *  : number of IP addresses assigned from netblock
+ *
+ *  ipUnassigned
+ *  : number of IP addresses in netblock which are not assigned
+ *
+ *  ipAvailable
+ *  : number of IP addresses in netblock available for assignment (i.e. not
+ *  : busyout and not currently assigend)
+ *
+ *  ipTotal
+ *  : total number of IP addresses in netblock
+ *
+ *  description
+ *  : human readable description of netblock
+ *
  */
 CREATE OR REPLACE FUNCTION netblock_audit
 (
@@ -697,11 +761,39 @@ $$;
 
 
 /*
- *  audits netblocks
+ *  audits pools
  *
- *  % SELECT * FROM netblock_audit();
+ *  % SELECT * FROM netblock_pool();
+ *
+ *  Returns table with the following columns:
+ *
+ *  poolName
+ *  : name of pool which owns IP address
+ *
+ *  regionType
+ *  : one of 'regional', 'non-regional', and 'TOTAL'
+ *
+ *  ipAssignedFree
+ *  : number of IP addresses which are currently not busyout and are assigend
+ *  : to sessions
+ *
+ *  ipAssignedBusy
+ *  : number of IP addresses which are currently busyout and are assigend to
+ *  : sessions
+ *
+ *  ipAvailable
+ *  : number of IP addresses which are currently not busyout and not assigned to
+ *  : a session
+ *
+ *  ipBusyout
+ *  : number of IP addresses currently marked as busyout
+ *
+ *  ipTotal
+ *  : total number of IP addresses
+ *
+ *  description
+ *  : human readable description of record
  *
- *  Returns table of netblocks
  */
 CREATE OR REPLACE FUNCTION pool_audit
 (
@@ -709,7 +801,7 @@ CREATE OR REPLACE FUNCTION pool_audit
    RETURNS          TABLE
    (
                     poolName              VARCHAR(32),
-                    addressType           TEXT,
+                    regionType            TEXT,
                     ipAssignedFree        BIGINT,
                     ipAssignedBusy        BIGINT,
                     ipAvailable           BIGINT,
@@ -790,7 +882,7 @@ BEGIN
                             INNER JOIN       netblock       AS nb2
                                ON            nb2.id         =  ip.netblockId
                             WHERE            nb2.regionId   IS NULL
-                               AND           nb2.poolId     IS NULL
+                               AND           nb2.poolId     = pool.id
                                AND           nb2.busyout    IS FALSE
                                AND           ip.nasId       IS NOT NULL
                         )                    AS ipAssignFree,
@@ -839,7 +931,7 @@ BEGIN
          FROM           netblock
          INNER JOIN     pool
             ON          pool.id           =   netblock.poolId
-         WHERE          netblock.regionId IS NULL;
+         WHERE          netblock.regionId IS NULL
       UNION ALL
       SELECT DISTINCT   pool.name            AS poolName,
                         'ALL'                AS addressType,
@@ -848,7 +940,7 @@ BEGIN
                             FROM             ip
                             INNER JOIN       netblock       AS nb2
                                ON            nb2.id         =  ip.netblockId
-                            WHERE            nb2.poolId     IS NULL
+                            WHERE            nb2.poolId     =  pool.id
                                AND           nb2.busyout    IS FALSE
                                AND           ip.nasId       IS NOT NULL
                         )                    AS ipAssignFree,
@@ -900,11 +992,39 @@ $$;
 
 
 /*
- *  audits netblocks
+ *  audits regions
  *
- *  % SELECT * FROM netblock_audit();
+ *  % SELECT * FROM netblock_region();
+ *
+ *  Returns table with the following columns:
+ *
+ *  poolName
+ *  : name of pool which owns IP address
+ *
+ *  regionName
+ *  : name of region
+ *
+ *  ipAssignedFree
+ *  : number of IP addresses which are currently not busyout and are assigend
+ *  : to sessions
+ *
+ *  ipAssignedBusy
+ *  : number of IP addresses which are currently busyout and are assigend to
+ *  : sessions
+ *
+ *  ipAvailable
+ *  : number of IP addresses which are currently not busyout and not assigned to
+ *  : a session
+ *
+ *  ipBusyout
+ *  : number of IP addresses currently marked as busyout
+ *
+ *  ipTotal
+ *  : total number of IP addresses
+ *
+ *  description
+ *  : human readable description of record
  *
- *  Returns table of netblocks
  */
 CREATE OR REPLACE FUNCTION region_audit
 (