1 files changed, 104 insertions, 26 deletions

diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 9a92ef8..00288b0 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -231,7 +231,7 @@ CREATE INDEX
 /*
  *  trigger functions for ip table
  */
-CREATE OR REPLACE FUNCTION ip_trigger_delete
+CREATE OR REPLACE FUNCTION ip_before_delete
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -249,7 +249,7 @@ END;
 $$;
 
 
-CREATE OR REPLACE FUNCTION ip_trigger_insert
+CREATE OR REPLACE FUNCTION ip_before_insert
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -262,6 +262,15 @@ BEGIN
    NEW.nasId        := NULL;
    NEW.modified     := TIMESTAMP 'epoch';
 
+   -- verifies netblock contains IP address
+   PERFORM          id
+      FROM          netblock
+      WHERE         id = NEW.netblockId
+         AND        network >>= NEW.address;
+  IF NOT FOUND THEN
+      RAISE EXCEPTION 'invalid netblock';
+  END IF;
+
    -- determines mask for single IP address
    IF ( family(NEW.address) != 4 ) THEN
       RAISE EXCEPTION 'only IPv4 addresses are supported';
@@ -272,7 +281,7 @@ END;
 $$;
 
 
-CREATE OR REPLACE FUNCTION ip_trigger_truncate
+CREATE OR REPLACE FUNCTION ip_before_truncate
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -284,12 +293,13 @@ END;
 $$;
 
 
-CREATE OR REPLACE FUNCTION ip_trigger_update
+CREATE OR REPLACE FUNCTION ip_before_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
    AS               $$
 DECLARE
+   ipAssignable     BOOLEAN;
 BEGIN
 
    -- prevents IP address from being modified
@@ -297,8 +307,32 @@ BEGIN
       RAISE EXCEPTION 'address cannot be modified';
    END IF;
 
+   -- verifies netblock contains IP address
+   IF ( NEW.netblockId != NEW.netblockId ) THEN
+      SELECT        assignable      INTO ipAssignable
+         FROM       netblock
+         WHERE      id      =   NEW.netblockId
+            AND     network >>= NEW.address;
+      IF ipAssignable IS NULL THEN
+         RAISE EXCEPTION 'invalid netblock';
+      END IF;
+      IF ipAssignable IS TRUE THEN
+         PERFORM    id
+            FROM    netblock
+            WHERE   assignable IS TRUE
+               AND  network >>= NEW.address;
+         IF FOUND THEN
+            RAISE EXCEPTION 'cannot associate assignable netblock if an unassignable netblock contains IP address';
+         END IF;
+      END IF;
+   END IF;
+
    -- update timestamp
-   NEW.modified := current_timestamp;
+   IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN
+      NEW.modified := current_timestamp;
+   ELSE
+      NEW.modified := OLD.modified;
+   END IF;
 
    RETURN NEW;
 END;
@@ -308,7 +342,7 @@ $$;
 /*
  *  trigger functions for nas table
  */
-CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update
+CREATE OR REPLACE FUNCTION nas_before_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -327,7 +361,7 @@ $$;
 /*
  *  trigger functions for netblock table
  */
-CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update
+CREATE OR REPLACE FUNCTION netblock_before_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -353,7 +387,7 @@ BEGIN
      RAISE EXCEPTION 'conflict with existing network regions';
    END IF;
 
-   -- verifies that new region does not conflict with new network
+   -- verifies that new pool does not conflict with new network
    SELECT           count(id) INTO resultCount
       FROM          netblock
       WHERE         poolId != NEW.poolId
@@ -366,6 +400,45 @@ BEGIN
      RAISE EXCEPTION 'conflict with existing network pools';
    END IF;
 
+   -- verifies that IP address will not be orphaned
+   PERFORM          netblockId
+      FROM          ip
+      WHERE         ip.netblockId = NEW.id
+         AND        NOT ip.address <<= NEW.network;
+   IF FOUND THEN
+      RAISE EXCEPTION 'changing network will orphan existing IP addresses';
+   END IF;
+
+   -- updates IP Association of netblock if an update and is now assignable
+   IF ( ( OLD.assignable IS FALSE ) AND ( NEW.assignable IS TRUE ) ) THEN
+      UPDATE        ip
+         SET        netblockId      =   nb.id
+         FROM       ip
+         INNER JOIN netblock        AS  nb
+            ON      nb.network      >>= ip.address
+         WHERE      ip.address      <<= NEW.network
+            AND     nb.assignable   IS  TRUE;
+   END IF;
+
+   RETURN NEW;
+END;
+$$;
+
+
+CREATE OR REPLACE FUNCTION netblock_after_insert_or_update
+( )
+   RETURNS          trigger
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+BEGIN
+   -- updates IP association of netblock is not assignable
+   IF ( NEW.assignable IS FALSE ) THEN
+      UPDATE        ip
+         SET        netblockId = NEW.id
+         WHERE      address <<= NEW.network;
+   END IF;
+
    RETURN NEW;
 END;
 $$;
@@ -374,7 +447,7 @@ $$;
 /*
  *  trigger functions for pool table
  */
-CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update
+CREATE OR REPLACE FUNCTION pool_before_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -393,7 +466,7 @@ $$;
 /*
  *  trigger functions for region table
  */
-CREATE OR REPLACE FUNCTION region_trigger_insert_or_update
+CREATE OR REPLACE FUNCTION region_before_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -416,58 +489,63 @@ $$;
 -- ////////////////
 
 -- address table triggers
-CREATE TRIGGER      ip_trigger_delete
+CREATE TRIGGER      ip_before_delete
    BEFORE           DELETE
    ON               ip
    FOR EACH         ROW
-   EXECUTE          PROCEDURE ip_trigger_delete();
-CREATE TRIGGER      ip_trigger_insert
+   EXECUTE          PROCEDURE ip_before_delete();
+CREATE TRIGGER      ip_before_insert
    BEFORE           INSERT
    ON               ip
    FOR EACH         ROW
-   EXECUTE          PROCEDURE ip_trigger_insert();
-CREATE TRIGGER      ip_trigger_truncate
+   EXECUTE          PROCEDURE ip_before_insert();
+CREATE TRIGGER      ip_before_truncate
    BEFORE           TRUNCATE
    ON               ip
    FOR EACH         STATEMENT
-   EXECUTE          PROCEDURE ip_trigger_truncate();
-CREATE TRIGGER      ip_trigger_update
+   EXECUTE          PROCEDURE ip_before_truncate();
+CREATE TRIGGER      ip_before_update
    BEFORE           UPDATE
    ON               ip
    FOR EACH         ROW
-   EXECUTE          PROCEDURE ip_trigger_update();
+   EXECUTE          PROCEDURE ip_before_update();
 
 
 -- netblock table triggers
-CREATE TRIGGER      netblock_trigger_insert_or_update
+CREATE TRIGGER      netblock_before_insert_or_update
    BEFORE           INSERT OR UPDATE
    ON               netblock
    FOR EACH         ROW
-   EXECUTE          PROCEDURE netblock_trigger_insert_or_update();
+   EXECUTE          PROCEDURE netblock_before_insert_or_update();
+CREATE TRIGGER      netblock_after_insert_or_update
+   AFTER            INSERT OR UPDATE
+   ON               netblock
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE netblock_after_insert_or_update();
 
 
 -- nas table triggers
-CREATE TRIGGER      nas_trigger_insert_or_update
+CREATE TRIGGER      nas_before_insert_or_update
    BEFORE           INSERT OR UPDATE
    ON               nas
    FOR EACH         ROW
-   EXECUTE          PROCEDURE nas_trigger_insert_or_update();
+   EXECUTE          PROCEDURE nas_before_insert_or_update();
 
 
 -- pool table triggers
-CREATE TRIGGER      pool_trigger_insert_or_update
+CREATE TRIGGER      pool_before_insert_or_update
    BEFORE           INSERT OR UPDATE
    ON               pool
    FOR EACH         ROW
-   EXECUTE          PROCEDURE pool_trigger_insert_or_update();
+   EXECUTE          PROCEDURE pool_before_insert_or_update();
 
 
 -- region table triggers
-CREATE TRIGGER      region_trigger_insert_or_update
+CREATE TRIGGER      region_before_insert_or_update
    BEFORE           INSERT OR UPDATE
    ON               region
    FOR EACH         ROW
-   EXECUTE          PROCEDURE region_trigger_insert_or_update();
+   EXECUTE          PROCEDURE region_before_insert_or_update();
 
 
 /* end of sql */