Finishing ip assignment functions

3 files changed, 60 insertions, 24 deletions

diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index 57352d8..d0a992b 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -89,12 +89,18 @@ SELECT              netblock_add
                     ( netAddress    := '209.193.4.128/25',
                       poolName      := 'Reserved',
                       netBusyout    := TRUE,
-                      addIpEntries  := FALSE
+                      addIpEntries  := TRUE
                     );
 
 
 -- create example explicit IP reservations
 SELECT              ip_assign
+                    ( assignIp      := '209.193.4.3/32',
+                      assignNas     := 'br1.sol',
+                      assignUser    := 'busy1@example.net',
+                      assignForce   := TRUE
+                    );
+SELECT              ip_assign
                     ( assignIp      := '209.193.4.8/32',
                       assignNas     := 'br1.sol',
                       assignUser    := 'jdoe8@example.net'
@@ -126,6 +132,29 @@ SELECT              ip_assign
                     );
 
 
+-- auto-assign IP reservations
+SELECT              ip_autoassign
+                    ( assignUser    := 'autoassign1@example.net',
+                      assignNas     := 'br1.sol',
+                      assignPool    := 'dhcp'
+                    );
+SELECT              ip_autoassign
+                    ( assignUser    := 'autoassign2@example.net',
+                      assignNas     := 'br1.sol',
+                      assignPool    := 'dhcp'
+                    );
+SELECT              ip_autoassign
+                    ( assignUser    := 'autoassign3@example.net',
+                      assignNas     := 'br1.sol',
+                      assignPool    := 'dhcp'
+                    );
+SELECT              ip_autoassign
+                    ( assignUser    := 'autoassign4@example.net',
+                      assignNas     := 'br1.sol',
+                      assignPool    := 'dhcp'
+                    );
+
+
 -- 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' );
diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index 7ec18a6..df9e27b 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -54,6 +54,7 @@ DECLARE
    resultAddress    INET;
    ipAdded          INTEGER;
    ipMask           INTEGER;
+   ipOctet4         INET;
 BEGIN
 
    -- verifies input
@@ -81,26 +82,30 @@ BEGIN
    END IF;
 
    -- sets starting values
-   ipAdded          := 0;
-   ipAddress        := set_masklen( network(ipNetwork), 32 );
+   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;
+      ipOctet4         := ipAddress & '0.0.0.255';
+
+      IF ( ( ipOctet4 != '0.0.0.0' ) AND ( ipOctet4 != '0.0.0.255' ) ) THEN
+         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;
       END IF;
-      ipAddress     := ipAddress   + 1;
+      ipAddress        := ipAddress   + 1;
 
    END LOOP;
 
@@ -361,12 +366,12 @@ BEGIN
          INTO       assignedIp
          FROM       ip
          INNER JOIN netblock         AS   nb
-            ON      nb.network       >>=  ip.address
+            ON      nb.id            =    ip.netblockId
          WHERE      ip.nasId         IS   NULL
             AND     ip.username      LIKE lower( assignUser )
             AND     nb.poolId        =    assignPoolId
             AND     nb.regionId      =    assignRegionId
-            AND     nb.assignable    =    TRUE
+            AND     nb.busyout       =    FALSE
          ORDER BY   ip.modified      DESC,
                     ip.address       ASC
          LIMIT      1;
@@ -378,11 +383,11 @@ BEGIN
          INTO       assignedIp
          FROM       ip
          INNER JOIN netblock         AS   nb
-            ON      nb.network       >>=  ip.address
+            ON      nb.id            =    ip.netblockId
          WHERE      ip.nasId         IS   NULL
             AND     nb.poolId        =    assignPoolId
             AND     nb.regionId      =    assignRegionId
-            AND     nb.assignable    =    TRUE
+            AND     nb.busyout       =    FALSE
          ORDER BY   ip.modified      ASC,
                     ip.address       ASC
          LIMIT      1;
@@ -394,12 +399,12 @@ BEGIN
          INTO       assignedIp
          FROM       ip
          INNER JOIN netblock         AS   nb
-            ON      nb.network       >>=  ip.address
+            ON      nb.id            =    ip.netblockId
          WHERE      ip.nasId         IS   NULL
             AND     ip.username      LIKE lower( assignUser )
             AND     nb.poolId        =    assignPoolId
             AND     nb.regionId      IS   NULL
-            AND     nb.assignable    =    TRUE
+            AND     nb.busyout       =    FALSE
          ORDER BY   ip.modified      DESC,
                     ip.address       ASC
          LIMIT      1;
@@ -411,11 +416,11 @@ BEGIN
          INTO       assignedIp
          FROM       ip
          INNER JOIN netblock         AS   nb
-            ON      nb.network       >>=  ip.address
+            ON      nb.id            =    ip.netblockId
          WHERE      ip.nasId         IS   NULL
             AND     nb.poolId        =    assignPoolId
             AND     nb.regionId      IS   NULL
-            AND     nb.assignable    =    TRUE
+            AND     nb.busyout       =    FALSE
          ORDER BY   ip.modified      ASC,
                     ip.address       ASC
          LIMIT      1;
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index d64dfd5..7348ca3 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -328,7 +328,9 @@ BEGIN
    END IF;
 
    -- update timestamp
-   IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN
+   -- RAISE EXCEPTION 'Old.nasId = %; NEW.nasId = %;', OLD.nasId, NEW.nasId;
+   IF ( ( OLD.nasId IS DISTINCT FROM NEW.nasId ) OR
+        ( OLD.username IS DISTINCT FROM NEW.username ) ) THEN
       NEW.modified := current_timestamp;
    END IF;