splitting user functions into separate source file

3 files changed, 555 insertions, 556 deletions

diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
new file mode 100644
index 0000000..bbfd60f
--- /dev/null
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -0,0 +1,536 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  ippool-schema.sql - builds database schema for RADIUS IP Pools
+ *
+ *  Usage:  psql -f ippool-schema.sql radius_ippool
+ */
+
+
+SET ROLE radius;
+SET client_min_messages TO WARNING;
+\set ON_ERROR_STOP on
+
+
+-- /////////////////
+-- //             //
+-- //  Functions  //
+-- //             //
+-- /////////////////
+
+/*
+ *  Creates IP entries for the given network block
+ *
+ *  % SELECT ip_add_network(
+ *       ipNetwork   := '198.51.100.0/24'
+ *    );
+ *  % SELECT ip_add_network(
+ *       ipNetblockId := (
+ *                         SELECT       id
+ *                            FROM      netblock
+ *                            WHERE     network = '198.51.100.0/24'
+ *                       )
+ *    );
+ *
+ *  ipNetwork
+ *  : network address and network size being added to database
+ *
+ *  ipNetblockId
+ *  : reference to netblock to add to database
+ *
+ *  Returns the number of new IP addresses registered
+ */
+CREATE OR REPLACE FUNCTION ip_add_network
+(
+   ipNetwork        CIDR            DEFAULT NULL,
+   ipNetblockId     INTEGER         DEFAULT NULL
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   ipAddress        INET;
+   resultAddress    INET;
+   ipAdded          INTEGER;
+   ipMask           INTEGER;
+BEGIN
+
+   -- verifies input
+   IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN
+      RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId';
+   END IF;
+
+   -- determines netblock
+   if ( ipNetblockId IS NULL ) THEN
+      SELECT        id              INTO ipNetblockId
+        FROM        netblock
+        WHERE       network         >>= ipNetwork;
+   ELSE
+      SELECT        network         INTO ipNetwork
+         FROM       netblock
+         WHERE      id              = ipNetblockId;
+   END IF;
+   if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN
+      RAISE EXCEPTION 'specified netblock does not exist';
+   END IF;
+
+   -- restricts access to IPv4 addresses
+   IF ( family(ipNetwork) != 4 ) THEN
+      RAISE EXCEPTION 'only IPv4 addresses are supported';
+   END IF;
+
+   -- sets starting values
+   ipAdded          := 0;
+   ipAddress        := set_masklen( network(ipNetwork), 32 );
+
+   -- loops through individual IP addresses
+   LOOP
+      EXIT WHEN     ipAddress > set_masklen( broadcast(ipNetwork), 32 );
+
+      INSERT INTO   ip
+                    ( address,
+                      netblockId
+                    )
+         SELECT     ipAddress,
+                    ipNetblockId
+            WHERE   NOT EXISTS
+                    ( SELECT address FROM ip WHERE address = ipAddress )
+          RETURNING address INTO resultAddress;
+      IF ( resultAddress IS NOT NULL ) THEN
+         ipAdded    := ipAdded + 1;
+      END IF;
+      ipAddress     := ipAddress   + 1;
+
+   END LOOP;
+
+   RETURN ipAdded;
+END;
+$$;
+
+
+/*
+ *  Assigns specific IP address to specified user/session
+ *
+ *  % SELECT ip_assign(
+ *       assignIp         := '198.51.100.1/32',
+ *       assignUser       := 'jdoe@acsalaska.net'
+ *    );
+ *  % SELECT ip_assign(
+ *       assignIp         := '198.51.100.1/32',
+ *       assignUser       := 'jdoe@acsalaska.net',
+ *       assignPortLimit  := 5,
+ *       assignNas        := 'br1.cwc',
+ *       assignSess       := '596c3b82-75df-11e9-bcbb-6003089b9f24',
+ *       assignForce      := FALSE
+ *    );
+ *
+ *  assignIp
+ *  : IP address to assign to user
+ *
+ *  assignUser
+ *  : user to assign IP address
+ *
+ *  assignPortLimit
+ *  : specifies maximum IP assignments per user allowed
+ *
+ *  assignNas
+ *  : session NAS (attempt to guess if not specified)
+ *
+ *  assignSess
+ *  : RADIUS session
+ *
+ *  assignForce
+ *  : force IP assignment, even if netblock is inactive (i.e. static assignments)
+ *
+ *  Returns TRUE if assigned and FALSE if not assigned
+ */
+CREATE OR REPLACE FUNCTION ip_assign
+(
+   assignIp         INET,
+   assignUser       TEXT,
+   assignPortLimit  INTEGER         DEFAULT 2,
+   assignNas        TEXT            DEFAULT NULL,
+   assignSess       TEXT            DEFAULT NULL,
+   assignForce      BOOLEAN         DEFAULT FALSE
+)
+   RETURNS          BOOLEAN
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   assignNasId      INTEGER;
+   assignPortCount  INTEGER;
+BEGIN
+
+   -- sets defaults
+   IF ( assignSess IS NULL ) THEN
+      assignSess   := 'user:' || assignUser;
+   END IF;
+
+   -- tallies existing sessions
+   SELECT           count(id)
+      INTO          assignPortCount
+      FROM          ip
+      WHERE         ip.username       LIKE   assignUser
+         AND        ip.nasId          IS NOT NULL
+         AND        ip.address        !=     assignIp;
+   IF ( assignPortCount >= assignPortLimit ) THEN
+      RETURN FALSE;
+   END IF;
+
+   -- verifies IP address is not inactive
+   IF ( assignForce = FALSE ) THEN
+      SELECT           network
+         FROM          netblock
+         WHERE         netblock.network >>= assignIp
+            AND        netblock.active  IS  FALSE;
+      IF FOUND THEN
+         RETURN FALSE;
+      END IF;
+   END IF;
+
+   -- attempts to determine NAS
+   IF ( assignNas IS NOT NULL ) THEN
+      SELECT        id
+         INTO       assignNasId
+         FROM       nas
+         WHERE      name              LIKE assignNas;
+   ELSE
+      SELECT        nas.id
+         INTO       assignNasId
+         FROM       nas
+         INNER JOIN region
+            ON      region.id         = nas.regionId
+         INNER JOIN netblock
+            ON      netblock.regionId = regoin.id
+         WHERE      netblock.network  >>= assignIp;
+   END IF;
+   IF ( assignNasId IS NULL ) THEN
+      RETURN FALSE;
+   END IF;
+
+   -- updates record
+   UPDATE           ip
+      SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
+                    username      = assignUser,
+                    acctSession   = assignSess
+      WHERE         address       = set_masklen( assignIp, 32 )
+         AND        nasId         IS NULL;
+
+   RETURN FOUND;
+END;
+$$;
+
+
+/*
+ *  Auto-assigns IP address to specified user/session
+ *
+ *  % SELECT ip_autoassign(
+ *       assignUser       := 'jdoe@acsalaska.net',
+ *       assignNas        := 'br1.sol',
+ *       assignPool       := 'dhcp'
+ *    );
+ *  % SELECT ip_autoassign(
+ *       assignUser       := 'jdoe@acsalaska.net',
+ *       assignNas        := 'br1.sol',
+ *       assignPool       := 'dhcp',
+ *       assignPortLimit  := 5,
+ *       assignSess       := 'b4356b24-7670-11e9-84a0-6003089b9f24',
+ *       assignPrevIp     := TRUE
+ *    );
+ *
+ *  assignUser
+ *  : user to assign IP address
+ *
+ *  assignNas
+ *  : NAS to assign to IP address and implies region from which to pull IP
+ *
+ *  assignPool
+ *  : IP pool to assign from which to assign IP address
+ *
+ *  assignPortLimit
+ *  : specifies maximum IP assignments per user allowed
+ *
+ *  assignSess (default: auto-generated from username)
+ *  : RADIUS session
+ *
+ *  assignPrevIp (default: TRUE)
+ *  : attempt to re-assign user's previous IP address
+ *
+ *  Returns INET with value of assigned IP address
+ */
+CREATE OR REPLACE FUNCTION ip_autoassign
+(
+   assignUser       TEXT,
+   assignNas        TEXT,
+   assignPool       TEXT,
+   assignPortLimit  INTEGER,         DEFAULT 2,
+   assignSess       TEXT             DEFAULT NULL,
+   assignPrevIp     BOOLEAN          DEFAULT TRUE
+)
+   RETURNS          INET
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   assignedIp       INET;
+   assignNasId      INTEGER;
+BEGIN
+
+   -- sets defaults
+   IF ( assignSess IS NULL ) THEN
+      assignSess :=    'user:' || assignUser;
+   END IF;
+
+   -- search for user's previous address
+   IF ( assignPrevIp IS TRUE ) THEN
+      SELECT        ip.address,
+                    nas.id
+         INTO       assignedIp,
+                    assignNasId
+         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( assignUser )
+            AND     nas.name         LIKE lower( assignNas )
+            AND     pool.name        LIKE lower( assignPool )
+            AND     netblock.active  =    TRUE
+         ORDER BY   ip.modified      DESC,
+                    ip.address       ASC
+         LIMIT      1;
+   END IF;
+
+   -- if previous address is unavailable, find longest idle address
+   IF ( assignedIp IS NULL ) THEN
+      SELECT        ip.address,
+                    nas.id
+         INTO       assignedIp,
+                    assignNasId
+         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( assignUser )
+            AND     nas.name         LIKE lower( assignNas )
+            AND     pool.name        LIKE lower( assignPool )
+            AND     netblock.active  =    TRUE
+         ORDER BY   ip.modified      DESC,
+                    ip.address       ASC
+         LIMIT      1;
+      IF NOT FOUND THEN
+         RETURN NULL;
+      END IF;
+   END IF;
+
+   -- update record
+   UPDATE           ip
+      SET           nasId            =    assignNasId,
+                    username         =    assignUser,
+                    acctSession      =    assignSess
+      WHERE         address          =    assignedIp;
+   IF NOT FOUND THEN
+      RETURN NULL;
+   END IF;
+
+   RETURN assignedIp;
+END;
+$$;
+
+
+
+/*
+ *  updates timestamp of addresses
+ *
+ *  returns trigger
+ */
+CREATE OR REPLACE FUNCTION ip_release
+(
+   assignIp         INET
+)
+   RETURNS          BOOLEAN
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+BEGIN
+
+   -- updates record
+   UPDATE           ip
+      SET           nasId         = NULL,
+                    acctSession   = NULL
+      WHERE         address       = set_masklen( assignIp, 32 );
+
+   RETURN FOUND;
+END;
+$$;
+
+
+/*
+ *  Creates entry for NAS client
+ *
+ *  % SELECT nas_add(
+ *       nasName    := 'br1.cwc',
+ *       regionName := 'Anchorage'
+ *    );
+ *
+ *  nasName
+ *  : name of NAS being added to database
+ *
+ *  regionName
+ *  : the name of the region in which the NAS is physically located
+ *
+ *  Returns the `id` of the created nas client
+ */
+CREATE OR REPLACE FUNCTION nas_add
+(
+   nasName          VARCHAR(32),
+   regionName       VARCHAR(128)
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   nasRegionId      INTEGER;
+   returnId         INTEGER;
+BEGIN
+   -- searches for region id
+   SELECT           id      INTO nasRegionId
+      FROM          region
+      WHERE         name    LIKE lower(regionName);
+   IF ( nasRegionId IS NULL ) THEN
+      RAISE EXCEPTION 'invalid region';
+   END IF;
+
+   -- adds new pool
+   INSERT INTO      nas
+                    ( name,
+                      regionId
+                    )
+      VALUES        ( lower(nasName),
+                      nasRegionId
+                    )
+      RETURNING     id INTO returnId;
+
+   RETURN returnId;
+END;
+$$;
+
+
+/*
+ *  Creates entry for network block
+ *
+ *  % SELECT netblock_add(
+ *       netAddress    := '198.51.100.0/24',
+ *       poolName      := 'PPPoE'
+ *    );
+ *  % SELECT netblock_add(
+ *       netAddress    := '203.0.113.0/24',
+ *       poolName      := 'DHCP',
+ *       regionName    := 'Soldotna',
+ *       netActive     := FALSE,
+ *       netDesc       := 'Lab IP Pool',
+ *       addIpEntries  := TRUE
+ *    );
+ *
+ *  netAddress
+ *  : network address and network size being added to database
+ *
+ *  poolName
+ *  : IP pool network is being assigned
+ *
+ *  regionName
+ *  : the name of the region in which the network will physically serve
+ *
+ *  netActive
+ *  : TRUE if addresses within network are assignable, FALSE if addresses are
+ *  : not assignable
+ *
+ *  netDesc
+ *  : human readable description of the network
+ *
+ *  addIpEntries
+ *  : add coorisponding ip table entries for network
+ *
+ *  Returns the `id` of the created nas client
+ */
+CREATE OR REPLACE FUNCTION netblock_add
+(
+   netAddress       CIDR,
+   poolName         VARCHAR(32),
+   regionName       VARCHAR(32)  DEFAULT NULL,
+   netActive        BOOLEAN      DEFAULT TRUE,
+   netDesc          VARCHAR(128) DEFAULT NULL,
+   addIpEntries     BOOLEAN      DEFAULT TRUE
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   SECURITY         DEFINER
+   AS               $$
+DECLARE
+   netRegionId      INTEGER      DEFAULT NULL;
+   netPoolId        INTEGER;
+   returnId         INTEGER      DEFAULT 1;
+BEGIN
+
+   -- restricts access to IPv4 addresses
+   IF ( family(netAddress) != 4 ) THEN
+      RAISE EXCEPTION 'only IPv4 addresses are supported';
+   END IF;
+
+   -- searches for region id
+   if ( regionName IS NOT NULL ) THEN
+      SELECT        id      INTO netRegionId
+         FROM       region
+         WHERE      name    LIKE lower(regionName);
+   END IF;
+
+   -- searches for pool id
+   SELECT           id      INTO netPoolId
+      FROM          pool
+      WHERE         name    LIKE lower(poolName);
+   IF ( netPoolId IS NULL ) THEN
+      RAISE EXCEPTION 'invalid pool';
+   END IF;
+
+   -- adds new pool
+   INSERT INTO      netblock
+                    ( network,
+                      poolId,
+                      regionId,
+                      active,
+                      description
+                    )
+      VALUES        ( netAddress,
+                      netPoolId,
+                      netRegionId,
+                      netActive,
+                      netDesc
+                    )
+      RETURNING     id INTO returnId;
+
+   -- adds IP entries
+   if ( addIpEntries IS NOT FALSE ) THEN
+      PERFORM ip_add_network( ipNetblockId := returnId);
+   END IF;
+
+   RETURN returnId;
+END;
+$$;
+
+
+/* end of sql */
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 59914d3..fcf74ea 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -227,377 +227,14 @@ CREATE INDEX
                     );
 
 
--- /////////////////
--- //             //
--- //  Functions  //
--- //             //
--- /////////////////
+-- /////////////////////////
+-- //                     //
+-- //  Trigger Functions  //
+-- //                     //
+-- /////////////////////////
 
 /*
- *  Creates IP entries for the given network block
- *
- *  % SELECT ip_add_network(
- *       ipNetwork   := '198.51.100.0/24'
- *    );
- *  % SELECT ip_add_network(
- *       ipNetblockId := (
- *                         SELECT       id
- *                            FROM      netblock
- *                            WHERE     network = '198.51.100.0/24'
- *                       )
- *    );
- *
- *  ipNetwork
- *  : network address and network size being added to database
- *
- *  ipNetblockId
- *  : reference to netblock to add to database
- *
- *  Returns the number of new IP addresses registered
- */
-CREATE OR REPLACE FUNCTION ip_add_network
-(
-   ipNetwork        CIDR            DEFAULT NULL,
-   ipNetblockId     INTEGER         DEFAULT NULL
-)
-   RETURNS          INTEGER
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   ipAddress        INET;
-   resultAddress    INET;
-   ipAdded          INTEGER;
-   ipMask           INTEGER;
-BEGIN
-
-   -- verifies input
-   IF ( ( ipNetwork IS NULL ) AND ( ipNetblockId IS NULL ) ) THEN
-      RAISE EXCEPTION 'must specify either ipNetwork or ipNetblockId';
-   END IF;
-
-   -- determines netblock
-   if ( ipNetblockId IS NULL ) THEN
-      SELECT        id              INTO ipNetblockId
-        FROM        netblock
-        WHERE       network         >>= ipNetwork;
-   ELSE
-      SELECT        network         INTO ipNetwork
-         FROM       netblock
-         WHERE      id              = ipNetblockId;
-   END IF;
-   if ( ( ipNetblockId IS NULL ) OR ( ipNetwork IS NULL ) ) THEN
-      RAISE EXCEPTION 'specified netblock does not exist';
-   END IF;
-
-   -- restricts access to IPv4 addresses
-   IF ( family(ipNetwork) != 4 ) THEN
-      RAISE EXCEPTION 'only IPv4 addresses are supported';
-   END IF;
-
-   -- sets starting values
-   ipAdded          := 0;
-   ipAddress        := set_masklen( network(ipNetwork), 32 );
-
-   -- loops through individual IP addresses
-   LOOP
-      EXIT WHEN     ipAddress > set_masklen( broadcast(ipNetwork), 32 );
-
-      INSERT INTO   ip
-                    ( address,
-                      netblockId
-                    )
-         SELECT     ipAddress,
-                    ipNetblockId
-            WHERE   NOT EXISTS
-                    ( SELECT address FROM ip WHERE address = ipAddress )
-          RETURNING address INTO resultAddress;
-      IF ( resultAddress IS NOT NULL ) THEN
-         ipAdded    := ipAdded + 1;
-      END IF;
-      ipAddress     := ipAddress   + 1;
-
-   END LOOP;
-
-   RETURN ipAdded;
-END;
-$$;
-
-
-/*
- *  Assigns specific IP address to specified user/session
- *
- *  % SELECT ip_assign(
- *       assignIp         := '198.51.100.1/32',
- *       assignUser       := 'jdoe@acsalaska.net'
- *    );
- *  % SELECT ip_assign(
- *       assignIp         := '198.51.100.1/32',
- *       assignUser       := 'jdoe@acsalaska.net',
- *       assignPortLimit  := 5,
- *       assignNas        := 'br1.cwc',
- *       assignSess       := '596c3b82-75df-11e9-bcbb-6003089b9f24',
- *       assignForce      := FALSE
- *    );
- *
- *  assignIp
- *  : IP address to assign to user
- *
- *  assignUser
- *  : user to assign IP address
- *
- *  assignPortLimit
- *  : specifies maximum IP assignments per user allowed
- *
- *  assignNas
- *  : session NAS (attempt to guess if not specified)
- *
- *  assignSess
- *  : RADIUS session
- *
- *  assignForce
- *  : force IP assignment, even if netblock is inactive (i.e. static assignments)
- *
- *  Returns TRUE if assigned and FALSE if not assigned
- */
-CREATE OR REPLACE FUNCTION ip_assign
-(
-   assignIp         INET,
-   assignUser       TEXT,
-   assignPortLimit  INTEGER         DEFAULT 2,
-   assignNas        TEXT            DEFAULT NULL,
-   assignSess       TEXT            DEFAULT NULL,
-   assignForce      BOOLEAN         DEFAULT FALSE
-)
-   RETURNS          BOOLEAN
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   assignNasId      INTEGER;
-   assignPortCount  INTEGER;
-BEGIN
-
-   -- sets defaults
-   IF ( assignSess IS NULL ) THEN
-      assignSess   := 'user:' || assignUser;
-   END IF;
-
-   -- tallies existing sessions
-   SELECT           count(id)
-      INTO          assignPortCount
-      FROM          ip
-      WHERE         ip.username       LIKE   assignUser
-         AND        ip.nasId          IS NOT NULL
-         AND        ip.address        !=     assignIp;
-   IF ( assignPortCount >= assignPortLimit ) THEN
-      RETURN FALSE;
-   END IF;
-
-   -- verifies IP address is not inactive
-   IF ( assignForce = FALSE ) THEN
-      SELECT           network
-         FROM          netblock
-         WHERE         netblock.network >>= assignIp
-            AND        netblock.active  IS  FALSE;
-      IF FOUND THEN
-         RETURN FALSE;
-      END IF;
-   END IF;
-
-   -- attempts to determine NAS
-   IF ( assignNas IS NOT NULL ) THEN
-      SELECT        id
-         INTO       assignNasId
-         FROM       nas
-         WHERE      name              LIKE assignNas;
-   ELSE
-      SELECT        nas.id
-         INTO       assignNasId
-         FROM       nas
-         INNER JOIN region
-            ON      region.id         = nas.regionId
-         INNER JOIN netblock
-            ON      netblock.regionId = regoin.id
-         WHERE      netblock.network  >>= assignIp;
-   END IF;
-   IF ( assignNasId IS NULL ) THEN
-      RETURN FALSE;
-   END IF;
-
-   -- updates record
-   UPDATE           ip
-      SET           nasId         = ( SELECT id FROM nas WHERE name LIKE assignNas ),
-                    username      = assignUser,
-                    acctSession   = assignSess
-      WHERE         address       = set_masklen( assignIp, 32 )
-         AND        nasId         IS NULL;
-
-   RETURN FOUND;
-END;
-$$;
-
-
-/*
- *  Auto-assigns IP address to specified user/session
- *
- *  % SELECT ip_autoassign(
- *       assignUser       := 'jdoe@acsalaska.net',
- *       assignNas        := 'br1.sol',
- *       assignPool       := 'dhcp'
- *    );
- *  % SELECT ip_autoassign(
- *       assignUser       := 'jdoe@acsalaska.net',
- *       assignNas        := 'br1.sol',
- *       assignPool       := 'dhcp',
- *       assignPortLimit  := 5,
- *       assignSess       := 'b4356b24-7670-11e9-84a0-6003089b9f24',
- *       assignPrevIp     := TRUE
- *    );
- *
- *  assignUser
- *  : user to assign IP address
- *
- *  assignNas
- *  : NAS to assign to IP address and implies region from which to pull IP
- *
- *  assignPool
- *  : IP pool to assign from which to assign IP address
- *
- *  assignPortLimit
- *  : specifies maximum IP assignments per user allowed
- *
- *  assignSess (default: auto-generated from username)
- *  : RADIUS session
- *
- *  assignPrevIp (default: TRUE)
- *  : attempt to re-assign user's previous IP address
- *
- *  Returns INET with value of assigned IP address
- */
-CREATE OR REPLACE FUNCTION ip_autoassign
-(
-   assignUser       TEXT,
-   assignNas        TEXT,
-   assignPool       TEXT,
-   assignPortLimit  INTEGER,         DEFAULT 2,
-   assignSess       TEXT             DEFAULT NULL,
-   assignPrevIp     BOOLEAN          DEFAULT TRUE
-)
-   RETURNS          INET
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   assignedIp       INET;
-   assignNasId      INTEGER;
-BEGIN
-
-   -- sets defaults
-   IF ( assignSess IS NULL ) THEN
-      assignSess :=    'user:' || assignUser;
-   END IF;
-
-   -- search for user's previous address
-   IF ( assignPrevIp IS TRUE ) THEN
-      SELECT        ip.address,
-                    nas.id
-         INTO       assignedIp,
-                    assignNasId
-         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( assignUser )
-            AND     nas.name         LIKE lower( assignNas )
-            AND     pool.name        LIKE lower( assignPool )
-            AND     netblock.active  =    TRUE
-         ORDER BY   ip.modified      DESC,
-                    ip.address       ASC
-         LIMIT      1;
-   END IF;
-
-   -- if previous address is unavailable, find longest idle address
-   IF ( assignedIp IS NULL ) THEN
-      SELECT        ip.address,
-                    nas.id
-         INTO       assignedIp,
-                    assignNasId
-         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( assignUser )
-            AND     nas.name         LIKE lower( assignNas )
-            AND     pool.name        LIKE lower( assignPool )
-            AND     netblock.active  =    TRUE
-         ORDER BY   ip.modified      DESC,
-                    ip.address       ASC
-         LIMIT      1;
-      IF NOT FOUND THEN
-         RETURN NULL;
-      END IF;
-   END IF;
-
-   -- update record
-   UPDATE           ip
-      SET           nasId            =    assignNasId,
-                    username         =    assignUser,
-                    acctSession      =    assignSess
-      WHERE         address          =    assignedIp;
-   IF NOT FOUND THEN
-      RETURN NULL;
-   END IF;
-
-   RETURN assignedIp;
-END;
-$$;
-
-
-
-/*
- *  updates timestamp of addresses
- *
- *  returns trigger
- */
-CREATE OR REPLACE FUNCTION ip_release
-(
-   assignIp         INET
-)
-   RETURNS          BOOLEAN
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-BEGIN
-
-   -- updates record
-   UPDATE           ip
-      SET           nasId         = NULL,
-                    acctSession   = NULL
-      WHERE         address       = set_masklen( assignIp, 32 );
-
-   RETURN FOUND;
-END;
-$$;
-
-
-/*
- *  updates timestamp of addresses
- *
- *  returns trigger
+ *  trigger functions for ip table
  */
 CREATE OR REPLACE FUNCTION ip_trigger_delete
 ( )
@@ -617,11 +254,6 @@ END;
 $$;
 
 
-/*
- *  forces initial values on insert
- *
- *  returns trigger
- */
 CREATE OR REPLACE FUNCTION ip_trigger_insert
 ( )
    RETURNS          trigger
@@ -646,11 +278,6 @@ END;
 $$;
 
 
-/*
- *  verifies no assigned IP addresses before truncating
- *
- *  returns trigger
- */
 CREATE OR REPLACE FUNCTION ip_trigger_truncate
 ( )
    RETURNS          trigger
@@ -664,11 +291,6 @@ END;
 $$;
 
 
-/*
- *  updates timestamp of addresses
- *
- *  returns trigger
- */
 CREATE OR REPLACE FUNCTION ip_trigger_update
 ( )
    RETURNS          trigger
@@ -691,61 +313,7 @@ $$;
 
 
 /*
- *  Creates entry for NAS client
- *
- *  % SELECT nas_add(
- *       nasName    := 'br1.cwc',
- *       regionName := 'Anchorage'
- *    );
- *
- *  nasName
- *  : name of NAS being added to database
- *
- *  regionName
- *  : the name of the region in which the NAS is physically located
- *
- *  Returns the `id` of the created nas client
- */
-CREATE OR REPLACE FUNCTION nas_add
-(
-   nasName          VARCHAR(32),
-   regionName       VARCHAR(128)
-)
-   RETURNS          INTEGER
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   nasRegionId      INTEGER;
-   returnId         INTEGER;
-BEGIN
-   -- searches for region id
-   SELECT           id      INTO nasRegionId
-      FROM          region
-      WHERE         name    LIKE lower(regionName);
-   IF ( nasRegionId IS NULL ) THEN
-      RAISE EXCEPTION 'invalid region';
-   END IF;
-
-   -- adds new pool
-   INSERT INTO      nas
-                    ( name,
-                      regionId
-                    )
-      VALUES        ( lower(nasName),
-                      nasRegionId
-                    )
-      RETURNING     id INTO returnId;
-
-   RETURN returnId;
-END;
-$$;
-
-
-/*
- *  updates timestamp of addresses
- *
- *  returns trigger
+ *  trigger functions for nas table
  */
 CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update
 ( )
@@ -764,111 +332,7 @@ $$;
 
 
 /*
- *  Creates entry for network block
- *
- *  % SELECT netblock_add(
- *       netAddress    := '198.51.100.0/24',
- *       poolName      := 'PPPoE'
- *    );
- *  % SELECT netblock_add(
- *       netAddress    := '203.0.113.0/24',
- *       poolName      := 'DHCP',
- *       regionName    := 'Soldotna',
- *       netActive     := FALSE,
- *       netDesc       := 'Lab IP Pool',
- *       addIpEntries  := TRUE
- *    );
- *
- *  netAddress
- *  : network address and network size being added to database
- *
- *  poolName
- *  : IP pool network is being assigned
- *
- *  regionName
- *  : the name of the region in which the network will physically serve
- *
- *  netActive
- *  : TRUE if addresses within network are assignable, FALSE if addresses are
- *  : not assignable
- *
- *  netDesc
- *  : human readable description of the network
- *
- *  addIpEntries
- *  : add coorisponding ip table entries for network
- *
- *  Returns the `id` of the created nas client
- */
-CREATE OR REPLACE FUNCTION netblock_add
-(
-   netAddress       CIDR,
-   poolName         VARCHAR(32),
-   regionName       VARCHAR(32)  DEFAULT NULL,
-   netActive        BOOLEAN      DEFAULT TRUE,
-   netDesc          VARCHAR(128) DEFAULT NULL,
-   addIpEntries     BOOLEAN      DEFAULT TRUE
-)
-   RETURNS          INTEGER
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   netRegionId      INTEGER      DEFAULT NULL;
-   netPoolId        INTEGER;
-   returnId         INTEGER      DEFAULT 1;
-BEGIN
-
-   -- restricts access to IPv4 addresses
-   IF ( family(netAddress) != 4 ) THEN
-      RAISE EXCEPTION 'only IPv4 addresses are supported';
-   END IF;
-
-   -- searches for region id
-   if ( regionName IS NOT NULL ) THEN
-      SELECT        id      INTO netRegionId
-         FROM       region
-         WHERE      name    LIKE lower(regionName);
-   END IF;
-
-   -- searches for pool id
-   SELECT           id      INTO netPoolId
-      FROM          pool
-      WHERE         name    LIKE lower(poolName);
-   IF ( netPoolId IS NULL ) THEN
-      RAISE EXCEPTION 'invalid pool';
-   END IF;
-
-   -- adds new pool
-   INSERT INTO      netblock
-                    ( network,
-                      poolId,
-                      regionId,
-                      active,
-                      description
-                    )
-      VALUES        ( netAddress,
-                      netPoolId,
-                      netRegionId,
-                      netActive,
-                      netDesc
-                    )
-      RETURNING     id INTO returnId;
-
-   -- adds IP entries
-   if ( addIpEntries IS NOT FALSE ) THEN
-      PERFORM ip_add_network( ipNetblockId := returnId);
-   END IF;
-
-   RETURN returnId;
-END;
-$$;
-
-
-/*
- *  adds contraints to netblocks
- *
- *  returns trigger
+ *  trigger functions for netblock table
  */
 CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update
 ( )
@@ -915,9 +379,7 @@ $$;
 
 
 /*
- *  updates timestamp of addresses
- *
- *  returns trigger
+ *  trigger functions for pool table
  */
 CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update
 ( )
@@ -936,9 +398,7 @@ $$;
 
 
 /*
- *  updates timestamp of addresses
- *
- *  returns trigger
+ *  trigger functions for region table
  */
 CREATE OR REPLACE FUNCTION region_trigger_insert_or_update
 ( )
@@ -1016,4 +476,5 @@ CREATE TRIGGER      region_trigger_insert_or_update
    FOR EACH         ROW
    EXECUTE          PROCEDURE region_trigger_insert_or_update();
 
+
 /* end of sql */
diff --git a/notes/projects/radius/sql/ippool.md b/notes/projects/radius/sql/ippool.md
index d5c1223..ce39c38 100644
--- a/notes/projects/radius/sql/ippool.md
+++ b/notes/projects/radius/sql/ippool.md
@@ -12,12 +12,13 @@ Objectives
 Files
 ----
 
-   * ippool.md           - This file
-   * ippool-database.sql - Creates database
-   * ippool-schema.sql   - Creates database schema (tables, indexes, etc)
-   * ippool-demo.sql     - Loads demo data
-   * ippool-queries.sql  - Common or useful queries
-   * radius-roles.sql    - Common database roles for RADIUS
+   * ippool.md            - This file
+   * ippool-database.sql  - Creates database
+   * ippool-schema.sql    - Creates database schema (tables, indexes, etc)
+   * ippool-functions.sql - Creates end-user/end-application functions
+   * ippool-demo.sql      - Loads demo data
+   * ippool-queries.sql   - Common or useful queries
+   * radius-roles.sql     - Common database roles for RADIUS
 
 
 Initializing New Database
@@ -31,6 +32,7 @@ Initializing New Database
 
       psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql
       psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql    radius_ippool
+      psql -h raddbtest.prv.acsalaska.net -f ippool-functions.sql radius_ippool
 
  * Create RADIUS pools using `pool_add()`.
  * Create RADIUS regions using `region_add()`.