Simplifiing schema

2 files changed, 174 insertions, 209 deletions

diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index 3875eed..e6eb48a 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -12,16 +12,16 @@ SET ROLE radius;
 
 
 -- create example pools
-SELECT pool_add( poolName := 'DHCP' );
-SELECT pool_add( poolName := 'PPPoE' );
-SELECT pool_add( poolName := 'reserved' );
+INSERT INTO pool ( name ) VALUES ( 'DHCP' );
+INSERT INTO pool ( name ) VALUES ( 'PPPoE' );
+INSERT INTO pool ( name ) VALUES ( 'reserved' );
 
 
 -- create example regions
-SELECT region_add( regionName := 'Anchorage' );
-SELECT region_add( regionName := 'Soldotna' );
-SELECT region_add( regionName := 'Fairbanks' );
-SELECT region_add( regionName := 'No where' );
+INSERT INTO region ( name ) VALUES ( 'Anchorage' );
+INSERT INTO region ( name ) VALUES ( 'Soldotna' );
+INSERT INTO region ( name ) VALUES ( 'Fairbanks' );
+INSERT INTO region ( name ) VALUES ( 'No where' );
 
 
 -- create example NAS clients
@@ -118,8 +118,8 @@ SELECT              ip_assign
 
 
 -- add missing IP addresses to netblock
-SELECT              ip_add_network( ipNetwork := '209.112.192.0/19' );
-SELECT              ip_add_network( ipNetwork := '209.112.128.0/18' );
+-- SELECT              ip_add_network( ipNetwork := '209.112.192.0/19' );
+-- SELECT              ip_add_network( ipNetwork := '209.112.128.0/18' );
 
 
 /* end of sql */
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index b048b72..ea50e5d 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -19,14 +19,14 @@ SET client_min_messages TO WARNING;
 -- //////////////////
 
 
--- GRANT               CONNECT
---    ON DATABASE      radius_ippool
---    TO               radius_user;
+GRANT               CONNECT
+   ON DATABASE      radius_ippool
+   TO               radius_user;
 
 
-GRANT               USAGE
-   ON SCHEMA        public
-   TO               radius_user;
+--GRANT               USAGE
+--   ON SCHEMA        public
+--   TO               radius_user;
 
 
 ALTER DEFAULT PRIVILEGES
@@ -183,6 +183,9 @@ CREATE INDEX
  *  address
  *  : user assignable IP address
  *
+ *  netblockId
+ *  : reference to owning netblock
+ *
  *  modified
  *  : timestamp generated when entry is modified
  *
@@ -198,6 +201,7 @@ CREATE INDEX
 CREATE TABLE IF NOT EXISTS ip
 (
    address          INET,
+   netblockId       INTEGER         NOT NULL,
    modified         timestamp       WITH TIME ZONE NOT NULL
                                     DEFAULT TIMESTAMP 'epoch',
    username         VARCHAR(64),
@@ -206,12 +210,16 @@ CREATE TABLE IF NOT EXISTS ip
    PRIMARY KEY      ( address ),
    FOREIGN KEY      ( nasId )
       REFERENCES    nas
+                    ( id ),
+   FOREIGN KEY      ( netblockId )
+      REFERENCES    netblock
                     ( id )
 );
 CREATE INDEX
    IF NOT EXISTS    ip_index
    ON               ip
                     ( address,
+                      netblockId,
                       modified,
                       username,
                       acctSession,
@@ -231,15 +239,26 @@ CREATE INDEX
  *  % 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
+   ipNetwork        CIDR            DEFAULT NULL,
+   ipNetblockId     INTEGER         DEFAULT NULL
 )
    RETURNS          INTEGER
    LANGUAGE         plpgsql
@@ -247,17 +266,28 @@ CREATE OR REPLACE FUNCTION ip_add_network
    AS               $$
 DECLARE
    ipAddress        INET;
-   resultCount      INTEGER;
+   resultAddress    INET;
    ipAdded          INTEGER;
    ipMask           INTEGER;
 BEGIN
 
-   -- searches for a matching netblock
-   SELECT           count(network) INTO resultCount
-      FROM          netblock
-      WHERE         network >>= ipNetwork;
-   IF ( resultCount = 0 ) THEN
-      RAISE EXCEPTION 'matching netblock not found';
+   -- 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
@@ -273,16 +303,16 @@ BEGIN
    LOOP
       EXIT WHEN     ipAddress > set_masklen( broadcast(ipNetwork), 32 );
 
-      -- searches for existing IP address
-      SELECT        count( address ) INTO resultCount
-         FROM       ip
-         WHERE      address = ipAddress;
-
-      -- defines IP address if not defined and counts results
-      IF ( resultCount = 0 ) THEN
-         INSERT INTO   ip
-                       ( address )
-            VALUES     ( ipAddress );
+      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;
@@ -335,7 +365,7 @@ $$;
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION ip_trigger
+CREATE OR REPLACE FUNCTION ip_trigger_delete
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -343,57 +373,58 @@ CREATE OR REPLACE FUNCTION ip_trigger
 DECLARE
 BEGIN
 
-   -- prevents IP address from being modified
-   IF ( ( TG_OP = 'UPDATE' ) AND ( NEW.address != OLD.address ) ) THEN
-      RAISE EXCEPTION 'address cannot be modified';
+   -- verifies ip is not assigned to a RADIUS accounting session
+   IF ( OLD.nasId IS NOT NULL ) THEN
+      RAISE EXCEPTION 'IP address is still assigned to NAS session';
    END IF;
 
+   RETURN NEW;
+END;
+$$;
+
+
+/*
+ *  forces initial values on insert
+ *
+ *  returns trigger
+ */
+CREATE OR REPLACE FUNCTION ip_trigger_insert
+( )
+   RETURNS          trigger
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+BEGIN
+
    -- forces initial values
-   IF ( TG_OP = 'INSERT' ) THEN
-      NEW.username     := NULL;
-      NEW.acctSession  := NULL;
-      NEW.nasId        := NULL;
-   END IF;
+   NEW.username     := NULL;
+   NEW.acctSession  := NULL;
+   NEW.nasId        := NULL;
+   NEW.modified     := TIMESTAMP 'epoch';
 
    -- determines mask for single IP address
    IF ( family(NEW.address) != 4 ) THEN
       RAISE EXCEPTION 'only IPv4 addresses are supported';
    END IF;
 
-   -- update timestamp
-   IF ( TG_OP = 'UPDATE' ) THEN
-      NEW.modified := current_timestamp;
-   ELSIF ( TG_OP = 'INSERT' ) THEN
-      NEW.modified := TIMESTAMP 'epoch';
-   END IF;
-
    RETURN NEW;
 END;
 $$;
 
 
 /*
- *  updates timestamp of addresses
+ *  verifies no assigned IP addresses before truncating
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION ip_trigger_delete
+CREATE OR REPLACE FUNCTION ip_trigger_truncate
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
    AS               $$
 DECLARE
 BEGIN
-   -- verifies ip is not assigned to a RADIUS accounting session
-   IF ( OLD.acctSession IS NOT NULL ) THEN
-      RAISE EXCEPTION 'IP address is still assigned to accounting session';
-   END IF;
-
-   -- verifies ip is not assigned to a RADIUS accounting session
-   IF ( OLD.nasId IS NOT NULL ) THEN
-      RAISE EXCEPTION 'IP address is still assigned to NAS session';
-   END IF;
-
+   RAISE EXCEPTION 'DO NOT TRUNCATE IP TABLE';
    RETURN NEW;
 END;
 $$;
@@ -404,23 +435,22 @@ $$;
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION ip_trigger_truncate
+CREATE OR REPLACE FUNCTION ip_trigger_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
    AS               $$
 DECLARE
-   resultCount      INTEGER;
 BEGIN
-   -- searches for assigned IP addresses
-   SELECT           count( address ) INTO resultCount
-      FROM          ip
-      WHERE         acctSession IS NOT NULL
-         OR         nasId IS NOT NULL;
-   IF ( resultCount != 0 ) THEN
-      RAISE EXCEPTION 'IP addresses are still assigned to sessions';
+
+   -- prevents IP address from being modified
+   IF ( NEW.address != OLD.address ) THEN
+      RAISE EXCEPTION 'address cannot be modified';
    END IF;
 
+   -- update timestamp
+   NEW.modified := current_timestamp;
+
    RETURN NEW;
 END;
 $$;
@@ -479,6 +509,27 @@ $$;
 
 
 /*
+ *  updates timestamp of addresses
+ *
+ *  returns trigger
+ */
+CREATE OR REPLACE FUNCTION nas_trigger_insert_or_update
+( )
+   RETURNS          trigger
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+BEGIN
+
+   -- normalizes name
+   NEW.name = lower( NEW.name );
+
+   RETURN NEW;
+END;
+$$;
+
+
+/*
  *  Creates entry for network block
  *
  *  % SELECT netblock_add(
@@ -534,6 +585,11 @@ DECLARE
    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
@@ -567,7 +623,7 @@ BEGIN
 
    -- adds IP entries
    if ( addIpEntries IS NOT FALSE ) THEN
-      PERFORM ip_add_network( ipNetwork := netAddress );
+      PERFORM ip_add_network( ipNetblockId := returnId);
    END IF;
 
    RETURN returnId;
@@ -580,7 +636,7 @@ $$;
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION netblock_trigger
+CREATE OR REPLACE FUNCTION netblock_trigger_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
@@ -625,34 +681,20 @@ $$;
 
 
 /*
- *  adds contraints to netblocks
+ *  updates timestamp of addresses
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION netblock_trigger_delete
+CREATE OR REPLACE FUNCTION pool_trigger_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
    AS               $$
 DECLARE
-   resultCount      INTEGER;
 BEGIN
-   -- checks for IP addresses which would be orphaned
-   SELECT          count( address ) INTO resultCount
-      FROM         ip
-      WHERE        address             NOT IN
-                   (
-                     SELECT            address
-                        FROM           ip
-                        INNER JOIN     netblock
-                           ON          ip.address          <<= netblock.network
-                        WHERE          ip.address          <<= OLD.network
-                        AND            netblock.network    <>  OLD.network
-                   )
-      AND          address <<= OLD.network;
-   IF ( resultCount != 0 ) THEN
-      RAISE EXCEPTION 'IP addresses still defined within netblock';
-   END IF;
+
+   -- normalizes name
+   NEW.name = lower( NEW.name );
 
    RETURN NEW;
 END;
@@ -660,119 +702,22 @@ $$;
 
 
 /*
- *  adds contraints to netblocks
+ *  updates timestamp of addresses
  *
  *  returns trigger
  */
-CREATE OR REPLACE FUNCTION netblock_trigger_truncate
+CREATE OR REPLACE FUNCTION region_trigger_insert_or_update
 ( )
    RETURNS          trigger
    LANGUAGE         plpgsql
    AS               $$
 DECLARE
-   resultCount      INTEGER;
 BEGIN
-   -- checks for IP addresses which would be orphaned
-   SELECT          count( address ) INTO resultCount
-      FROM         ip;
-   IF ( resultCount != 0 ) THEN
-      RAISE EXCEPTION 'IP addresses still defined';
-   END IF;
-
-   RETURN NEW;
-END;
-$$;
-
-
-
-/*
- *  Creates entry for IP Pool
- *
- *  % SELECT pool_add(
- *       poolName  := 'dhcp'
- *    );
- *  % SELECT pool_add(
- *       poolName  := 'dhcp',
- *       poolDesc  := 'Pool of IP addresses for PPPoE connections'
- *    );
- *
- *  poolName
- *  : name of pool being created in the database
- *
- *  poolDesc
- *  : human readable description of the pool
- *
- *  Returns the `id` of the created pool
- */
-CREATE OR REPLACE FUNCTION pool_add
-(
-   poolName         VARCHAR(32),
-   poolDesc         VARCHAR(128) DEFAULT NULL
-)
-   RETURNS          INTEGER
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   returnId         INTEGER;
-BEGIN
-   -- adds new pool
-   INSERT INTO      pool
-                    ( name,
-                      description
-                    )
-      VALUES        ( lower(poolName),
-                      poolDesc
-                    )
-      RETURNING     id INTO returnId;
-
-   RETURN returnId;
-END;
-$$;
 
+   -- normalizes name
+   NEW.name = lower( NEW.name );
 
-/*
- *  Creates entry for geographic region
- *
- *  % SELECT region_add(
- *       regionName  := 'Anchorage'
- *    );
- *  % SELECT region_add(
- *       regionName  := 'Anchorage',
- *       regionDesc  := 'Anchorage Area'
- *    );
- *
- *  regionName
- *  : name of region being created in the database
- *
- *  regionDesc
- *  : human readable description of the region
- *
- *  Returns the `id` of the created pool
- */
-CREATE OR REPLACE FUNCTION region_add
-(
-   regionName       VARCHAR(32),
-   regionDesc       VARCHAR(128) DEFAULT NULL
-)
-   RETURNS          INTEGER
-   LANGUAGE         plpgsql
-   SECURITY         DEFINER
-   AS               $$
-DECLARE
-   returnId         INTEGER;
-BEGIN
-   -- adds new pool
-   INSERT INTO      region
-                    ( name,
-                      description
-                    )
-      VALUES        ( lower(regionName),
-                      regionDesc
-                    )
-      RETURNING     id INTO returnId;
-
-   RETURN returnId;
+   RETURN NEW;
 END;
 $$;
 
@@ -784,37 +729,57 @@ $$;
 -- ////////////////
 
 -- address table triggers
-CREATE TRIGGER      ip_trigger
-   BEFORE           INSERT OR UPDATE
-   ON               ip
-   FOR EACH         ROW
-   EXECUTE          PROCEDURE ip_trigger();
 CREATE TRIGGER      ip_trigger_delete
    BEFORE           DELETE
    ON               ip
    FOR EACH         ROW
    EXECUTE          PROCEDURE ip_trigger_delete();
+CREATE TRIGGER      ip_trigger_insert
+   BEFORE           INSERT
+   ON               ip
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE ip_trigger_insert();
 CREATE TRIGGER      ip_trigger_truncate
    BEFORE           TRUNCATE
    ON               ip
    FOR EACH         STATEMENT
    EXECUTE          PROCEDURE ip_trigger_truncate();
+CREATE TRIGGER      ip_trigger_update
+   BEFORE           UPDATE
+   ON               ip
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE ip_trigger_update();
+
 
 -- netblock table triggers
-CREATE TRIGGER      netblock_trigger
+CREATE TRIGGER      netblock_trigger_insert_or_update
    BEFORE           INSERT OR UPDATE
    ON               netblock
    FOR EACH         ROW
-   EXECUTE          PROCEDURE netblock_trigger();
-CREATE TRIGGER      netblock_trigger_delete
-   BEFORE           DELETE
-   ON               netblock
+   EXECUTE          PROCEDURE netblock_trigger_insert_or_update();
+
+
+-- nas table triggers
+CREATE TRIGGER      nas_trigger_insert_or_update
+   BEFORE           INSERT OR UPDATE
+   ON               nas
    FOR EACH         ROW
-   EXECUTE          PROCEDURE netblock_trigger_delete();
-CREATE TRIGGER      netblock_trigger_truncate
-   BEFORE           TRUNCATE
-   ON               netblock
-   FOR EACH         STATEMENT
-   EXECUTE          PROCEDURE netblock_trigger_truncate();
+   EXECUTE          PROCEDURE nas_trigger_insert_or_update();
+
+
+-- pool table triggers
+CREATE TRIGGER      pool_trigger_insert_or_update
+   BEFORE           INSERT OR UPDATE
+   ON               pool
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE pool_trigger_insert_or_update();
+
+
+-- region table triggers
+CREATE TRIGGER      region_trigger_insert_or_update
+   BEFORE           INSERT OR UPDATE
+   ON               region
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE region_trigger_insert_or_update();
 
 /* end of sql */