Expanding functions and triggers

3 files changed, 181 insertions, 17 deletions

diff --git a/notes/projects/radius/sql/ippool-database.sql b/notes/projects/radius/sql/ippool-database.sql
index b7d0654..660e676 100644
--- a/notes/projects/radius/sql/ippool-database.sql
+++ b/notes/projects/radius/sql/ippool-database.sql
@@ -9,6 +9,8 @@
 
 
 SET ROLE wheel;
+SET client_min_messages TO WARNING;
+\set ON_ERROR_STOP on
 
 
 CREATE ROLE radius_ippool
diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index 601ac48..4775d5f 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -25,6 +25,27 @@ SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' );
 SELECT nas_add( nasName := 'br1.sol',  regionName := 'Soldotna' );
 
 
+SELECT              netblock_add
+                    (
+                       netAddress   := '209.112.192.0/19',
+                       poolName     := 'DHCP',
+                       regionName   := 'Anchorage'
+                    );
+SELECT              netblock_add
+                    (
+                       netAddress   := '209.112.128.0/18',
+                       poolName     := 'DHCP',
+                       regionName   := 'Anchorage'
+                    );
+SELECT              netblock_add
+                    (
+                       netAddress   := '209.112.156.0/24',
+                       poolName     := 'DHCP',
+                       regionName   := 'Anchorage',
+                       netActive    := FALSE,
+                       netDesc      := 'Admin Network'
+                    );
+
 
 
 INSERT INTO block ( poolId, regionId, net, description ) VALUES
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 5dfc5e1..df2bfeb 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -8,6 +8,8 @@
 
 
 SET ROLE radius_ippool;
+SET client_min_messages TO WARNING;
+\set ON_ERROR_STOP on
 
 
 -- //////////////
@@ -103,15 +105,15 @@ CREATE INDEX
  *  id
  *  : The primary key of a specific entry
  *
+ *  network
+ *  : network address and network size of block
+ *
  *  poolId
  *  : reference to the pool type of the network
  *
  *  regionId
  *  : reference to the physical region served by the block
  *
- *  net
- *  : network address and network size of block
- *
  *  active
  *  : assignable to customer if true, disabled/inactive if false
  *
@@ -121,9 +123,9 @@ CREATE INDEX
 CREATE TABLE IF NOT EXISTS netblock
 (
    id               SERIAL,
+   network          CIDR            NOT NULL UNIQUE,
    poolId           INTEGER         NOT NULL,
    regionId         INTEGER,
-   network          CIDR            NOT NULL UNIQUE,
    active           BOOLEAN         NOT NULL DEFAULT TRUE,
    description      VARCHAR(32),
    PRIMARY KEY      ( id ),
@@ -150,39 +152,39 @@ CREATE INDEX
  *  address
  *  : user assignable IP address
  *
- *  poolId
- *  : reference to the pool type of the network
- *
- *  assigned
- *  : TRUE if currently assigned, FALSE if available
- *
  *  modified
  *  : timestamp generated when entry is modified
  *
  *  username
  *  : username currently assigned or last assigned address
+ *
+ *  acctSession
+ *  : RADIUS accounting session currently holding address
+ *
+ *  nasId
+ *  : reference to RADIUS NAS client currently holding address
  */
 CREATE TABLE IF NOT EXISTS ip
 (
    address          INET,
-   poolId           INTEGER         NOT NULL,
-   assigned         BOOLEAN         NOT NULL DEFAULT FALSE,
    modified         timestamp       WITH TIME ZONE NOT NULL
                                     DEFAULT TIMESTAMP 'epoch',
    username         VARCHAR(64),
+   acctSession      VARCHAR(32),
+   nasId            INTEGER,
    PRIMARY KEY      ( address ),
-   FOREIGN KEY      ( poolId )
-      REFERENCES    pool
+   FOREIGN KEY      ( nasId )
+      REFERENCES    nas
                     ( id )
 );
 CREATE INDEX
    IF NOT EXISTS    ip_index
    ON               ip
                     ( address,
-                      poolId,
-                      assigned,
                       modified,
-                      username
+                      username,
+                      acctSession,
+                      nasId
                     );
 
 
@@ -270,6 +272,137 @@ $$;
 
 
 /*
+ *  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'  
+ *    );
+ *
+ *  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
+ *
+ *  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
+)
+   RETURNS          INTEGER
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+   netRegionId      INTEGER      DEFAULT NULL;
+   netPoolId        INTEGER;
+   returnId         INTEGER      DEFAULT 1;
+BEGIN
+
+   -- 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;
+
+   RETURN returnId;
+END;
+$$;
+
+
+/*
+ *  updates timestamp of addresses
+ *
+ *  returns trigger
+ */
+CREATE OR REPLACE FUNCTION netblock_trigger
+( )
+   RETURNS          trigger
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+   resultCount      INTEGER;
+BEGIN
+
+   -- verifies that new region does not conflict with new network
+   SELECT           count(id) INTO resultCount
+      FROM          netblock
+      WHERE         regionId != NEW.regionId
+                    AND
+                    ( NEW.network >>= network
+                      OR
+                      NEW.network <<= network
+                    );
+   IF ( resultCount != 0 ) THEN
+     RAISE EXCEPTION 'conflict with existing network regions';
+   END IF;
+
+   -- verifies that new region does not conflict with new network
+   SELECT           count(id) INTO resultCount
+      FROM          netblock
+      WHERE         poolId != NEW.poolId
+                    AND
+                    ( NEW.network >>= network
+                      OR
+                      NEW.network <<= network
+                    );
+   IF ( resultCount != 0 ) THEN
+     RAISE EXCEPTION 'conflict with existing network pools';
+   END IF;
+
+   RETURN NEW;
+END;
+$$;
+
+
+/*
  *  Creates entry for IP Pool
  *
  *  % SELECT pool_add(
@@ -373,4 +506,12 @@ CREATE TRIGGER      ip_timestamp
    EXECUTE          PROCEDURE ip_timestamp();
 
 
+-- netblock table triggers
+CREATE TRIGGER      netblock_trigger
+   BEFORE           INSERT OR UPDATE
+   ON               netblock
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE netblock_trigger();
+
+
 /* end of sql */