Delimiter $$
Create Procedure isip(IN ipadd VARCHAR(50),IN ipanet VARCHAR(50),IN ipmask INT)
Begin
  Declare vid int;
  Declare var INT;
  SELECT (-1 << (32 - ipmask)) & INET_ATON(ipadd) = INET_ATON(ipanet) into vid;
  if vid then
      select ipadd, 'belong';	
  else
     select ipadd, 'not belong';	
  end if;
end;
$$

Delimiter $$
Create Procedure useip(IN ipadd VARCHAR(50),IN ipanet VARCHAR(50),IN ipmask INT,IN iid INT)
Begin
  Declare vid int;
  Declare var INT;
  SELECT (-1 << (32 - ipmask)) & INET_ATON(ipadd) = INET_ATON(ipanet) into vid;
  if vid then
      select iid,ipanet, ipmask;	
  end if;
end;
$$

Delimiter $$
Create Procedure findnet(IN ipadd VARCHAR(50))
Begin
  DECLARE done INT DEFAULT FALSE;
  DECLARE iid, isub, imask INT;
  DECLARE cur CURSOR FOR SELECT id,subnet,mask FROM subnets where mask < 33 ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    
Open cur;
WHILE not done  DO 
  FETCH cur INTO iid, isub, imask;
  /*select ipadd,iid,isub, imask;*/
  call useip(ipadd,INET_ATON(isub),imask,iid);
END WHILE;
Close cur; 
END;
$$

Delimiter $$
Create Procedure findnet(IN ipadd VARCHAR(50))
Begin
  DECLARE done INT DEFAULT FALSE;
  DECLARE iid, isub, imask INT;
  DECLARE cur CURSOR FOR SELECT id,subnet,mask FROM subnets;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;    
Open cur;
WHILE not done  DO 
  FETCH cur INTO iid, isub, imask;
  call useip(ipadd,INET_ATON(isub),imask,iid);
END WHILE;
Close cur; 
END;
$$

Delimiter $$
Create Procedure useip(IN ipadd VARCHAR(50),IN ipanet VARCHAR(50),IN ipmask INT,IN idd INT)
Begin
  Declare vid int;
  Declare var INT;
  SELECT (-1 << (32 - ipmask)) & INET_ATON(ipadd) = INET_ATON(ipanet) into vid;
  if vid then
      SELECT mask into var FROM ipaddresses join subnets on subnets.id=ipaddresses.subnetId where ip_addr=INET_ATON(ipadd);
      if var then      
        if ipmask >  var then      
          /*UPDATE ipaddresses SET subnetId=iid WHERE ip_addr = INET_ATON('ipadd') limit 1;*/
          select 'update', idd, ipadd, hostname;
        end if; 
      else
        select 'insert', idd, ipadd, hostname; 
       /*INSERT INTO ipaddresses (subnetId,ip_addr,dns_name) VALUES(iid,INET_ATON('ipadd'),hostname);*/
      end if;
  end if;
end;
$$

Delimiter $$
Create Procedure useip(IN ipadd VARCHAR(50),IN ipanet VARCHAR(50),IN ipmask INT,IN idd INT, IN hostname VARCHAR(50))
Begin
  Declare vid int;
  Declare var INT;
  SELECT (-1 << (32 - ipmask)) & INET_ATON(ipadd) = INET_ATON(ipanet) into vid;
  if vid then
      SELECT mask into var FROM ipaddresses join subnets on subnets.id=ipaddresses.subnetId where ip_addr=INET_ATON(ipadd);
      if var then      
        if (ipmask >  var) then      
          UPDATE ipaddresses SET subnetId=idd WHERE ip_addr = INET_ATON(ipadd) limit 1;
        end if; 
      else
	   INSERT INTO ipaddresses (subnetId,ip_addr,dns_name) VALUES(idd,INET_ATON(ipadd),hostname);
      end if;
  end if;
end;
$$