Fixing ip assignment tallies

1 files changed, 16 insertions, 4 deletions

diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index bbfd60f..a879cf3 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -169,7 +169,7 @@ BEGIN
    END IF;
 
    -- tallies existing sessions
-   SELECT           count(id)
+   SELECT           count( address )  AS     ipCount
       INTO          assignPortCount
       FROM          ip
       WHERE         ip.username       LIKE   assignUser
@@ -181,10 +181,10 @@ BEGIN
 
    -- verifies IP address is not inactive
    IF ( assignForce = FALSE ) THEN
-      SELECT           network
+      PERFORM          network
          FROM          netblock
          WHERE         netblock.network >>= assignIp
-            AND        netblock.active  IS  FALSE;
+            AND        netblock.active  =   FALSE;
       IF FOUND THEN
          RETURN FALSE;
       END IF;
@@ -265,7 +265,7 @@ CREATE OR REPLACE FUNCTION ip_autoassign
    assignUser       TEXT,
    assignNas        TEXT,
    assignPool       TEXT,
-   assignPortLimit  INTEGER,         DEFAULT 2,
+   assignPortLimit  INTEGER          DEFAULT 2,
    assignSess       TEXT             DEFAULT NULL,
    assignPrevIp     BOOLEAN          DEFAULT TRUE
 )
@@ -276,6 +276,7 @@ CREATE OR REPLACE FUNCTION ip_autoassign
 DECLARE
    assignedIp       INET;
    assignNasId      INTEGER;
+   assignPortCount  INTEGER;
 BEGIN
 
    -- sets defaults
@@ -283,6 +284,17 @@ BEGIN
       assignSess :=    'user:' || assignUser;
    END IF;
 
+   -- tallies existing sessions
+   SELECT           count( address )
+      INTO          assignPortCount
+      FROM          ip
+      WHERE         ip.username       LIKE   assignUser
+         AND        ip.nasId          IS NOT NULL
+         AND        ip.address        !=     assignIp;
+   IF ( assignPortCount >= assignPortLimit ) THEN
+      RETURN NULL;
+   END IF;
+
    -- search for user's previous address
    IF ( assignPrevIp IS TRUE ) THEN
       SELECT        ip.address,