all things cbl

…Are da bomb!

I can’t believe I have lived without SP’s until MySQL 5.0. I mean, it’s like incredibly easy now to make code more efficient and productive.

Case in point. We have this Radius authentication server. Where Dialup and DSL users on our system get authenticated. The queries to get people online have been getting more and more complex as we have grown. First we added content filtering, then we want to add monthly timeouts for users..but then we have to account for users that have purchased extra hours for this month.. It’s honestly been a nightmare to deal with. Oh and the biggest problem.. let me tell you.. is the fact that users are inconsistent with their login usernames. joeuser@infowest.com could login as joeuser@infowest.com or simply joeuser. So I have to account for that. Not a biggie, but then we have these @netutah.com users that we got a few years back so it adds even more code to the Radius server config file. Then to top it all off we have these ’special dialup’ users that are broadband accounts that need to dialup while on the road..but want to keep their DSL still connected. So, that means that there’s another exception.. ANYWAY, it turned out to be like 6 different super-big queries that the system would run max per user authentication attempt.

Example query:

SELECT u.password,dialup.idle_timeout,if((monthlyTime > 0),\
  ((dialup.monthlyTime+(if((hours.addedTime IS NULL),0,hours.addedTime)))-\
  (if(SUM(dt.SESSIONTIME) IS NULL,0,SUM(dt.SESSIONTIME)))),dialup.session_timeout) \
  AS newSessionTimeout,dialup.simultaneous_use,if((xstop.class IS NULL),"IW.SUNSET",CONCAT(xstop.class,"")) \
  AS xstop_class, if((dialup.staticIP = 1 AND ips.locationID = 1000 AND ips.protocolID = 100 ),ips.ip,NULL) \
  AS static_ip,dialup.inacl AS acl1,dialup.outacl AS acl2 FROM authenticate.users AS u \
  LEFT JOIN authenticate.service_dialup AS dialup ON u.userID = dialup.userID \
  LEFT JOIN authenticate.service_static_ips AS ips ON u.userID = ips.userID AND ips.protocolID = 100 \
  LEFT JOIN authenticate.service_xstop AS xstop ON xstop.userID = u.userID AND xstop.statusID = 1 \
  LEFT JOIN authenticate.service_dialup_hours AS hours ON \
  (u.userID = hours.userID AND hours.monthYear = date_format(CURDATE(),'%Y-%m')) \
   LEFT JOIN radius.DATATRANSMIT AS dt ON (dt.USERNAME = u.userID AND dt.TYPE = 'DIALUP' AND \
   dt.DATE LIKE date_format(CURDATE(),'%Y-%m-%%')) \
    WHERE u.userID = '%n' AND dialup.statusID = 1 GROUP BY u.userID HAVING (newSessionTimeout >= 0);

Can we say nightmarish to maintain and update? Especially since I have 5 or 6 of these per location with 5-7 locations to update. Anyway, it’s been a pain..

Then along came MySQL 5.x with its stored procedure functionality. First I was like, “How can I reduce my code to be more easier to maintain, yet keep Radius config down to a minimum..??”

Well, the answer is this:

CALL radius.dialup_auth("%n","%P","IW.BRIANHEAD","DIALUP");

I have one of those for BrianHead Dialup and one for BrianHead Broadband dialup.. so two function calls PER location!! 6 to 2.. not bad for simplifying. Oh and the code for Radius is much cleaner to read now.

So how does the function look? Is it crazy like before? Multi-database joins and the whole 9 yards? Not really.

DROP PROCEDURE dialup_auth;
delimiter $
CREATE PROCEDURE dialup_auth(IN MYuserID VARCHAR(64), IN MYpass VARCHAR(128), IN MYclass VARCHAR(128), IN MYtype VARCHAR(32))
  DETERMINISTIC  CONTAINS SQL
  BEGIN
   DECLARE classAppend VARCHAR(12);
   DECLARE userExists INT;
   DECLARE newUser VARCHAR(64);
   DECLARE returnVal VARCHAR(64);
   DECLARE avPairs VARCHAR(255);
   DECLARE myAVNAS VARCHAR(32);
   DECLARE myCount INT;
   DECLARE hasXstop INT;
   SET userExists = 0;

   SELECT 1 INTO userExists FROM authenticate.users
      WHERE userID=MYuserID AND clearpasswd = MYpass;
   IF (userExists != 1) THEN
     SELECT 1 INTO userExists FROM authenticate.users
        WHERE userID = CONCAT(MYuserID,"@infowest.com") AND clearpasswd = MYpass;
     IF (userExists != 1) THEN
       SELECT 1 INTO userExists FROM authenticate.users
          WHERE userID = CONCAT(MYuserID,"@netutah.com") AND clearpasswd = MYpass;
       IF (userExists = 1) THEN
    	-- We have a match for @netutah.com
        SELECT CONCAT(MYuserID,"@netutah.com") INTO newUser;
        SELECT "#NU" INTO classAppend;
       ELSE
          SELECT NULL INTO returnVal;
       END IF;
     ELSEIF (userExists = 1) THEN
      -- We have a match for @infowest.com
      SELECT CONCAT(MYuserID,"@infowest.com") INTO newUser;
      SELECT "#IW" INTO classAppend;
     ELSE
       SELECT NULL INTO returnVal;
     END IF;
   ELSEIF (userExists = 1) THEN
     -- We have a match for the user as is:
     SELECT MYuserID INTO newUser;
     SELECT "" INTO classAppend;
   ELSE
     SELECT NULL INTO returnVal;
   END IF;

   IF (returnVal = NULL) THEN
     -- We were unable to find a match, so return NULL.
     SELECT NULL;
   ELSE 

     -- We found a match, now do our queries:
     IF (MYtype = "DIALUP") THEN
       SELECT
        u.password,
        d.idle_timeout,
        radius_session_timeout(newUser,MYtype,d.monthlyTime) AS newSessionTimeout,
        d.simultaneous_use,
        if((x.class IS NULL),CONCAT(MYclass,classAppend),CONCAT(xstop_class_build(newUser),classAppend)) AS xstop_class,
        get_static_ip(newUser,MYclass,MYtype) AS static_ip,
        d.inacl AS inacl,
        d.outacl AS outacl
        FROM authenticate.users AS u
        NATURAL JOIN authenticate.service_dialup AS d
        LEFT JOIN authenticate.service_xstop AS x ON (x.userID = u.userID AND x.statusID = 1)
        WHERE u.userID = newUser AND d.statusID = 1
        GROUP BY u.userID
        HAVING (newSessionTimeout >= 0);
     ELSEIF (MYtype = "BBDIAL") THEN
       SELECT
        u.password AS password,
        bb.idle_timeout AS idleTime,
        radius_session_timeout(newUser,MYtype,bb.monthlyTime) AS newSessionTimeout,
        bb.simultaneous_use+1 AS simultaneousUsage,
        if((x.class IS NULL),CONCAT(MYclass,classAppend),CONCAT(xstop_class_build(newUser),classAppend)) AS xstop_class,
        get_static_ip(newUser,MYclass,MYtype) AS static_ip,
        bb.inacl AS inacl,
        bb.outacl AS outacl
        FROM authenticate.users AS u
        NATURAL JOIN authenticate.service_brdbnd_dial AS bb
        LEFT JOIN authenticate.service_xstop AS x ON (x.userID = u.userID AND x.statusID = 1)
        WHERE u.userID = newUser AND bb.statusID = 1
        GROUP BY u.userID
        HAVING (newSessionTimeout >= 0);
     ELSE
       SELECT NULL;
     END IF;
   END IF;
  END $
delimiter ;

You can see I made a few extra functions for finding the correct sessiontimeouts, rather than joining multi-databases into the query. Oh and another function for the ‘Class’ Attribute for our filtering service. Then there’s the staticIP function. So yeah, I reused code. Made code more portable for multi-locations. And I love it!

Tonight, I’m going to implement this into the full radius config. Not just for BrianHead anymore.. I’m excited.